Excel import MySQL data loss
Last night did not solve a night, come here for help, thank you.
for ($j =3; $j <= $highestRow; $j + +)//table header not read
{
$a = $objPHPExcel->getactivesheet ()->getcell ("a". $j)->getvalue ();
$a =iconv ("UTF-8", "Gbk//ignore", $a);//transcoding
$b = $objPHPExcel->getactivesheet ()->getcell ("B". $j)->getvalue ();
$b =iconv ("UTF-8", "Gbk//ignore", $b);//transcoding
$c = $objPHPExcel->getactivesheet ()->getcell ("C". $j)->getvalue ();
$d = $objPHPExcel->getactivesheet ()->getcell ("D". $j)->getvalue ();
$e = $objPHPExcel->getactivesheet ()->getcell ("E". $j)->getvalue ();
$sql = "INSERT into Paee_course VALUES ('". $a. "', '". $b. "')"; /, ". $c.", ". $d.", ". $e."
echo $sql. " $j
";
mysql_query ($sql);
/*if (!mysql_query ($sql))
{
Re_result ("Import data failed, please try again later or contact the Administrator");
}*/
}
Problem:
1, the output of the page is all output is successful, but import the database when only imported 67, for advice
2, last night page output for garbled, import database is garbled, I converted the page output is normal, but after the import database or garbled, but the morning when the import is not garbled, I did not change the code, what is the reason?
Thank you, everyone.
------Solution--------------------
1, garbled problem
Phpexcel default read data with Utf-8 encoding, no special need to change
If MySQL is set to the GBK character set, it does not require manual transcoding after reading. Simply execute set names UTF8 after connecting to the database. MySQL will automatically complete the transcoding work
Reduce the code and reduce the probability of a problem
2, data loss issues
To write robust code, the code that contains the error handling. At least this is necessary.
mysql_query ($sql) or Die (Mysql_error ());
To see what went wrong.
After you have read the data, insert it into the database without any processing
So if there is a special character "'" in the data, is it not going to go wrong?
------Solution--------------------
That's because there's too much data in your watch.
Your primary key is a signed long shape (int) that is changed to an unsigned Unsigned int to increase the capacity by a factor of
2147483647 is 2 of 31 square-1
Which is the maximum number that a signed long shaping can represent.
Unsigned long shaping can be represented by 4294967296
Of course, the primary key is not negative.