PHP csv导入需要帮助-插入表中时，十进制值变为舍入值 [英] PHP csv import need help - Decimal values becomes rounded values when inserting into table
问题描述
我正在尝试使用PHP将数据从csv文件插入MySql表中，
请查看此图片-我的CSV文件.
我面临的问题是，在加载CSV文件时，net_sales列变为四舍五入并插入到表中.
请在插入后查看下面的MySql Table示例图像.
仅供参考，在这里，我仅显示net_sales列来解释我的问题，实际上我在表格和CSV中有更多列.
由于某些原因，我无法像这样的"insert into tran_detail (tran_id,tran_datetime,net_sales) values (...)";
所以我更喜欢这样的说法"INSERT INTO tran_detail (".implode(',', array_keys($data)).") VALUES('".implode('\',\'', array_map("convert",array_values($data)))."')";
这是我用于插入的PHP.请帮助在表格中插入带有小数点的值，就像在CSV中一样.
function convert($string)
{
return htmlspecialchars($string,ENT_QUOTES);
}
$columnArray = array();
$dataArray = array();
$firstRule = true;
while ($data = fgetcsv ($source, 1000, ","))
{
if($firstRule)
{
foreach($data as $columnName)
{
$columnArray[] = $columnName;
}
$firstRule = false;
}
else
{
$rule = array();
for($i = 0; $i < count($data) ; $i++)
{
$rule[$columnArray[$i]] = $data[$i];
}
$dataArray[] = $rule;
}
}
foreach($dataArray as $data)
{
$query = "INSERT INTO `tran_detail` (".implode('`,`', array_keys($data))."`) VALUES('".implode('\',\'', array_map("convert",array_values($data)))."')";
mysql_query($query) or mysql_error();
}
fclose($source);
这是我要这样做的方式:
<?php
/*
PDO named placeholders require that the array keys are matched
so we have to prefix them with a colon : as in 'field' becomes ':field'
the benefit here is the array key order is irrelevant,
so your csv could have the headers in any order.
*/
function prefixPdoArray(array $array){
return array_map(function($item){
return ':'.$item;
}, $array);
}
//PDO database driver
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';
$db = new PDO($dsn, $user, $password);
//header array should match DB fields
$header_map = [
'field1',
'field2',
'field3',
'field4',
];
$placeholders = prefixPdoArray($header_map);
//prepare the query outside of the loop
$stmt = $db->prepare('INSERT INTO `tran_detail` (`'.implode('`,`', $header_map).'`)VALUES('.implode(',', $placeholders).')');
/*
we can dynamically build the query because $header_map and $placeholders
are "canned" data, but you could just type it out as well.
if you do the SQL manually you can dump $header_map and $placeholders
and manually create $default_map. You could also dump this function
prefixPdoArray() and just move the array map to $headers.
so it would be a bit more efficient, but I thought I would show you
a proper way to build the query dynamically.
*/
$default_map = array_fill_keys($placeholders, '');
//read the first line
$headers = fgetcsv($source, 1000, ",");
//$header_count = count($csv_headers); //for error chcking if needed
//prefix csv headers
$headers = prefixPdoArray($headers);
while ($data = fgetcsv($source, 1000, ",")){
/*
array combine will throw an error if the header length
is different then the data length.
this indicates a missing or extra delimiter in the csv file.
you may or may not have to check for this condition
-------------------------------------
if( $header_count != count($data) ){ //do something on error }
*/
//map file data to file headers
$csv_mapped = array_combine( $headers, $data);
//map file row to database query
$csv_mapped = array_replace($default_map, $csv_mapped );
//execute the query
$stmt->execute($csv_mapped);
}
fclose($source);
请注意，我只能对此进行有限的测试(没有DB或文件)，因此出于测试和解释目的，这里是测试基本功能的代码.
<?php
function prefixPdoArray(array $array){
return array_map(function($item){
return ':'.$item;
}, $array);
}
//header array should match DB fields
$header_map = [
'field1',
'field2',
'field3',
'field4',
];
$placeholders = prefixPdoArray($header_map);
echo str_pad(' Placeholders ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($placeholders);
//prepare the query
echo "\n".str_pad(' Raw SQL ', 45, '-', STR_PAD_BOTH)."\n";
echo 'INSERT INTO `tran_detail` (`'.implode('`,`', $header_map).'`)VALUES('.implode(',', $placeholders).')';
$default_map = array_fill_keys($placeholders, '');
echo "\n\n".str_pad(' Default Map ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($default_map);
//(CANNED TEST DATA) read the first line
//example data for testing ( missing field1 ), and field3 out of order
$headers = [
'field3',
'field2',
'field4',
];
//prefix headers with placeholders
$headers = prefixPdoArray($headers);
echo "\n".str_pad(' CSV headers ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($headers);
//while ($data = fgetcsv($source, 1000, ",")){
//(CANNED TEST DATA) read the data line(s)
//example data for testing ( missing field1 ), and field3 out of order
$data = [
'value3',
'value2',
'value4',
];
echo "\n".str_pad(' CSV data ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($data);
$csv_mapped = array_combine( $headers, $data);
echo "\n".str_pad(' CSV mapped data ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($csv_mapped);
$csv_mapped = array_replace($default_map, $csv_mapped );
echo "\n".str_pad(' CSV filled data ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($csv_mapped);
//}
输出
--------------- Placeholders ----------------
array(4) {
[0]=> string(7) ":field1"
[1]=> string(7) ":field2"
[2]=> string(7) ":field3"
[3]=> string(7) ":field4"
}
------------------ Raw SQL ------------------
INSERT INTO `tran_detail` (`field1`,`field2`,`field3`,`field4`)VALUES(:field1,:field2,:field3,:field4)
---------------- Default Map ----------------
array(4) {
[":field1"]=> string(0) ""
[":field2"]=> string(0) ""
[":field3"]=> string(0) ""
[":field4"]=> string(0) ""
}
---------------- CSV headers ----------------
array(3) {
[0]=> string(7) ":field3"
[1]=> string(7) ":field2"
[2]=> string(7) ":field4"
}
----------------- CSV data ------------------
array(3) {
[0]=> string(6) "value3"
[1]=> string(6) "value2"
[2]=> string(6) "value4"
}
-------------- CSV mapped data --------------
array(3) {
[":field3"]=> string(6) "value3"
[":field2"]=> string(6) "value2"
[":field4"]=> string(6) "value4"
}
-------------- CSV filled data --------------
array(4) {
[":field1"]=> string(0) ""
[":field2"]=> string(6) "value2"
[":field3"]=> string(6) "value3"
[":field4"]=> string(6) "value4"
}
您可以在这里查看.
http://sandbox.onlinephpfunctions.com/code/ab868ac6c6fbf43d74cf62ef2b>
输出中最重要的部分是最后2个数组，因为您可以看到我们如何将数据映射到文件头，然后使用 如果不问的话，这应该很容易解释. 希望我能将它们之间以及数据库和文件中的所有东西都匹配起来，如果没有的话，那应该真的很近了.但这是相当复杂的代码，所以我可能会错过一些东西并非不可想象. 重要的是，这将使您以一种优雅的方式映射CSV数据，并避免任何SQL注入的烦恼. I am trying to insert data into MySql table from csv file using PHP, please see this image - my CSV file.
the problem i face is, while loading the CSV file, the net_sales column becomes rounded and inserted in the table. please see below sample image of MySql Table after inserting.
FYI, here i am showing only net_sales column for explaining my problem, actually i have more columns in the table and CSV. due to some reasons, i cant have a static insert statement like this so i prefer to have this statement this is the PHP i am using for inserting. please help to insert the value with decimals in table as it is in the CSV.
This is the way I would do this: Note I can only do limited testing on this ( no DB or files ), so for testing and explanation purposes here is the code for testing the basic functionality. Outputs You can check it out here. http://sandbox.onlinephpfunctions.com/code/ab868ac6c6fbf43d74cf62ef2907b0c72e1f59bf The most important part in the output is the last 2 arrays, as you can see how we map the data to the file headers, and then use the This should make it pretty self explanatory, if not feel free to ask. Hopefully I got everything matched up between them and for the DB and file stuff, if not it should be really close. But this is quite a bit of fairly complex code, so it's not inconceivable I may have missed something. The important thing is this will let you map out the CSV data in an elegant way, and avoid any SQL Injection nastyness. 这篇关于PHP csv导入需要帮助-插入表中时，十进制值变为舍入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！$default_map填写所有缺少的列.您可以在其中放置所需的任何默认值，例如null或您拥有的默认值，但您必须手动进行操作，而不是使用$default_map = array_fill_keys($placeholders, ''); "insert into tran_detail (tran_id,tran_datetime,net_sales) values (...)";"INSERT INTO tran_detail (".implode(',', array_keys($data)).") VALUES('".implode('\',\'', array_map("convert",array_values($data)))."')"; function convert($string)
{
return htmlspecialchars($string,ENT_QUOTES);
}
$columnArray = array();
$dataArray = array();
$firstRule = true;
while ($data = fgetcsv ($source, 1000, ","))
{
if($firstRule)
{
foreach($data as $columnName)
{
$columnArray[] = $columnName;
}
$firstRule = false;
}
else
{
$rule = array();
for($i = 0; $i < count($data) ; $i++)
{
$rule[$columnArray[$i]] = $data[$i];
}
$dataArray[] = $rule;
}
}
foreach($dataArray as $data)
{
$query = "INSERT INTO `tran_detail` (".implode('`,`', array_keys($data))."`) VALUES('".implode('\',\'', array_map("convert",array_values($data)))."')";
mysql_query($query) or mysql_error();
}
fclose($source);
<?php
/*
PDO named placeholders require that the array keys are matched
so we have to prefix them with a colon : as in 'field' becomes ':field'
the benefit here is the array key order is irrelevant,
so your csv could have the headers in any order.
*/
function prefixPdoArray(array $array){
return array_map(function($item){
return ':'.$item;
}, $array);
}
//PDO database driver
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';
$db = new PDO($dsn, $user, $password);
//header array should match DB fields
$header_map = [
'field1',
'field2',
'field3',
'field4',
];
$placeholders = prefixPdoArray($header_map);
//prepare the query outside of the loop
$stmt = $db->prepare('INSERT INTO `tran_detail` (`'.implode('`,`', $header_map).'`)VALUES('.implode(',', $placeholders).')');
/*
we can dynamically build the query because $header_map and $placeholders
are "canned" data, but you could just type it out as well.
if you do the SQL manually you can dump $header_map and $placeholders
and manually create $default_map. You could also dump this function
prefixPdoArray() and just move the array map to $headers.
so it would be a bit more efficient, but I thought I would show you
a proper way to build the query dynamically.
*/
$default_map = array_fill_keys($placeholders, '');
//read the first line
$headers = fgetcsv($source, 1000, ",");
//$header_count = count($csv_headers); //for error chcking if needed
//prefix csv headers
$headers = prefixPdoArray($headers);
while ($data = fgetcsv($source, 1000, ",")){
/*
array combine will throw an error if the header length
is different then the data length.
this indicates a missing or extra delimiter in the csv file.
you may or may not have to check for this condition
-------------------------------------
if( $header_count != count($data) ){ //do something on error }
*/
//map file data to file headers
$csv_mapped = array_combine( $headers, $data);
//map file row to database query
$csv_mapped = array_replace($default_map, $csv_mapped );
//execute the query
$stmt->execute($csv_mapped);
}
fclose($source);
<?php
function prefixPdoArray(array $array){
return array_map(function($item){
return ':'.$item;
}, $array);
}
//header array should match DB fields
$header_map = [
'field1',
'field2',
'field3',
'field4',
];
$placeholders = prefixPdoArray($header_map);
echo str_pad(' Placeholders ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($placeholders);
//prepare the query
echo "\n".str_pad(' Raw SQL ', 45, '-', STR_PAD_BOTH)."\n";
echo 'INSERT INTO `tran_detail` (`'.implode('`,`', $header_map).'`)VALUES('.implode(',', $placeholders).')';
$default_map = array_fill_keys($placeholders, '');
echo "\n\n".str_pad(' Default Map ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($default_map);
//(CANNED TEST DATA) read the first line
//example data for testing ( missing field1 ), and field3 out of order
$headers = [
'field3',
'field2',
'field4',
];
//prefix headers with placeholders
$headers = prefixPdoArray($headers);
echo "\n".str_pad(' CSV headers ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($headers);
//while ($data = fgetcsv($source, 1000, ",")){
//(CANNED TEST DATA) read the data line(s)
//example data for testing ( missing field1 ), and field3 out of order
$data = [
'value3',
'value2',
'value4',
];
echo "\n".str_pad(' CSV data ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($data);
$csv_mapped = array_combine( $headers, $data);
echo "\n".str_pad(' CSV mapped data ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($csv_mapped);
$csv_mapped = array_replace($default_map, $csv_mapped );
echo "\n".str_pad(' CSV filled data ', 45, '-', STR_PAD_BOTH)."\n";
var_dump($csv_mapped);
//}
--------------- Placeholders ----------------
array(4) {
[0]=> string(7) ":field1"
[1]=> string(7) ":field2"
[2]=> string(7) ":field3"
[3]=> string(7) ":field4"
}
------------------ Raw SQL ------------------
INSERT INTO `tran_detail` (`field1`,`field2`,`field3`,`field4`)VALUES(:field1,:field2,:field3,:field4)
---------------- Default Map ----------------
array(4) {
[":field1"]=> string(0) ""
[":field2"]=> string(0) ""
[":field3"]=> string(0) ""
[":field4"]=> string(0) ""
}
---------------- CSV headers ----------------
array(3) {
[0]=> string(7) ":field3"
[1]=> string(7) ":field2"
[2]=> string(7) ":field4"
}
----------------- CSV data ------------------
array(3) {
[0]=> string(6) "value3"
[1]=> string(6) "value2"
[2]=> string(6) "value4"
}
-------------- CSV mapped data --------------
array(3) {
[":field3"]=> string(6) "value3"
[":field2"]=> string(6) "value2"
[":field4"]=> string(6) "value4"
}
-------------- CSV filled data --------------
array(4) {
[":field1"]=> string(0) ""
[":field2"]=> string(6) "value2"
[":field3"]=> string(6) "value3"
[":field4"]=> string(6) "value4"
}
$default_map to fill in any missing columns. You can put whatever defaults in there you need, such as null or what have you, but you'll have to do it manually instead of using $default_map = array_fill_keys($placeholders, '');
