I'm trying to import a csv file that has a currency field into a mysql database, but for some reason the import only brings in zero's. I'd like to have this data imported as a decimal with two decimal places.
How should I format my insertion query to accomplish this? Currently, it looks something like this:
$sql = "INSERT INTO database SET
`SalePrice` = '".database_safe($record["SalePrice"])."';The format string for the file is "csv|44|1|34".
Any help is appreciated.
Hi David,
My database is already setup as DECIMAL(10,2) so I'm not quite sure why it isn't working for me. I just sent a Get It Coded request so you can see the csv file.
Thanks again for your help.
Hi Dan,
Thanks for the copy of your feed, I immediately noticed that the price field has the currency symbol "$" included, which makes it invalid for inserting into a MySQL field of type DECIMAL(10,2), so this needs to be stripped - that should be all it is. Rather than modify your SQL code, simply add the following line just before that point:
$record["SalePrice"] = str_replace("$","",$record["SalePrice"]);That should be all it is!
Cheers,
David
Great, thank you! I figured it was something simple, and that added code did the trick. Thanks again.
Hi Dan,
If not already, I would suggest making sure that `SalePrice` is DECIMAL(10,2). The following script would do the trick (using the database.inc.php library included with the Get It Coded MySQL examples)
<?phprequire("database.inc.php");
$sql = "ALTER TABLE `table` CHANGE `SalePrice` `SalePrice` DECIMAL(10,2) NOT NULL DEFAULT '0.00'";
database_queryModify($sql,$result);
?>
(don't forget to change `table` as required for your database)
Hope this helps!
Cheers,
David