Read tab delimited text file into MySQL table with PHP

Question

I am trying to read in a series of tab delimited text files into existing MySQL tables. The code I have is quite 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 "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1" error.

I echo out the code, paste it into a MySQL editor and it runs fine, it just won't execute from the PHP script.

What am I doing wrong??

Si

UPDATE: Here are the echoe'd SQL's:

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 it creating the correct number of rows in the table, but the values it inserts are empty...

Could this be an encoding issue in the source text file??

Answer 1

You don't need the string cast, the data will already be strings.

Make sure there are no quotes in the file data. Echo out the sql string before you run it to see if there's something obviously wrong.

Change the 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 the last two values.

Answer 2

Hi

I dont like you method in general. Maybe you fix your "first" problem with the missing rows. Whats about some special character like '" backslash or SQL injection? I think you should use prepared statements which PDO provides and call the "bindValue" of the statement. It is a stable and buildin PHP lib. Or you can use dbTube.org instead which is a graphical import tool.

Greeting

Shutter