Reading delimited text file in MySQL table using PHP
I am trying to read in a series of tab delimited text files into existing MySQL tables. The code I have is pretty simple:
$lines = file("import/file_to_import.txt");
foreach ($lines as $line_num => $line) {
if($line_num > 1) {
$arr = explode("\t", $line);
$sql = sprintf("INSERT INTO my_table VALUES('%s', '%s', '%s', %s, %s);", trim((string)$arr[0]), trim((string)$arr[1]), trim((string)$arr[2]), trim((string)$arr[3]), trim((string)$arr[4]));
mysql_query($sql, $database) or die(mysql_error());
}
}
But no matter what I do (hence the casting before each variable in the sprintf statement), I get the message "You have an error in your SQL syntax, check the manual corresponding to your MySQL server version for the correct syntax to use next to the error" on line 1 ".
I exit the code, paste it into the MySQL editor and it works fine, it just won't execute from the PHP script.
What am I doing wrong?
Si
UPDATE: Here are the echoe'd SQL queries:
INSERT INTO wheelbase (WheelBaseCode, LanguageCode, WheelBaseDescription) VALUES ('A1', 'GBEN', '2.50-2.99m')
INSERT INTO wheelbase (WheelBaseCode, LanguageCode, WheelBaseDescription) VALUES ('A2', 'GBEN', '3.00-3.49m')
INSERT INTO wheelbase (WheelBaseCode, LanguageCode, WheelBaseDescription) VALUES ('A3', 'GBEN', '3.50-3.99m')
INSERT INTO wheelbase (WheelBaseCode, LanguageCode, WheelBaseDescription) VALUES ('A4', 'GBEN', '4.00-4.49m')
Interestingly, I now have the creation of the correct number of rows in the table, but the values it inserts are empty ...
Could this be a coding issue in the original text file?
a source to share
You don't need a string, the data will already be strings.
Make sure there are no quotes in the files. Throw away the sql line before running it to see if there is something clearly wrong.
Change SQL to:
"INSERT INTO my_table (`field1Name`, `field2Name`, `field3Name`, `field4Name`, `field5Name`) VALUES('%s', '%s', '%s', '%s', '%s');"
This change includes the field names and quoting of the last two values.
a source to share
I don't like your method at all. You may have fixed your "first" missing lines problem. What is a special character such as backslash or SQL injection? I think you should use the prepared statements that PDO provides and call the "bindValue" expression. It is a stable and built-in PHP lib. Or you can instead use dbTube.org which is a graphical import tool.
greeting
Gate
a source to share