PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Hi, I'd like to import a tab delimited file into mysql. The following code appears to work (it deletes the old data) until it hits the import sequence, whence I get the error message 'Invalide DATA LOAD Query'. What's up doc? By the way, this is a great site. I haven't needed to use it that often, fortunately, but the responses are usually spot on. I like the layout too, which is easy to use, and I like the way the formatting works.
<?
# MySQL database User ID, Password and DB name
$sql_id = "root";
/*$sql_pwd = "";
$sql_db = "testimport";*/
# Connect to the database
$db=mysql_connect ("localhost") or die(mysql_error());
mysql_select_db("testimport") or die(mysql_error());
# Delete the current content of the table
$query = "DELETE FROM importtable";
$result=mysql_query($query) or die ("Invalid DELETE query");
# Optimize the current table (recover empty space)
$query = "OPTIMIZE TABLE importtable";
$result=mysql_query($query) or die ("Invalid OPTIMIZE query");
# Load local comma separated, fields enclosed by quotes text database - File has to be in the same directory of this file
$query = "LOAD DATA LOCAL INFILE 'testfile.txt' INTO TABLE importtable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";
$result=mysql_query($query) or die ("Invalid DATA LOAD query");
# Get how many records are present in the table now
$result = mysql_db_query('$sql_id',"SELECT * from importtable") or die ("Invalid SELECT query");
$rows_count = mysql_num_rows($result);
echo "Records: $rows_count"; mysql_free_result($result);
?>
// instead of
$result=mysql_query($query) or die ("Invalid DATA LOAD query");
// use
$result=mysql_query($query) or die ("Invalid DATA LOAD query BECAUSE:<hr/>".MySQL_error($result)."<hr/>");
If you're still flummoxed, paste the resulting error here.
Thanks, that's a good tip to use mysql own error messengering system. The new error message was
Invalid DATA LOAD query BECAUSE:
--------------------------------------------------------------------------------
The used command is not allowed with this MySQL version
Third result on Google for "mysql load data" says:
The "LOAD DATA LOCAL INFILE" statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.
There's also an example script that might work for you there: