Importing tab delimted data into mysql using php

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!

Moderator: General Moderators

Post Reply
SoreE yes
Forum Commoner
Posts: 32
Joined: Wed Oct 11, 2006 3:59 am

Importing tab delimted data into mysql using php

Post by SoreE yes »

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.

Code: Select all

<?

# 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);

?>
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

Remember: "Invalid DATA LOAD query" is YOUR error message, not PHP's or MySQL's.

One way to help debug this yourself is to use the MySQL_error() function:

Code: Select all

//  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.

Cheers,
Kieran
SoreE yes
Forum Commoner
Posts: 32
Joined: Wed Oct 11, 2006 3:59 am

Post by SoreE yes »

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
but I don't know what this refers to. Any ideas?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

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:

http://www.modwest.com/help/kb6-253.html

Cheers,
Kieran
SoreE yes
Forum Commoner
Posts: 32
Joined: Wed Oct 11, 2006 3:59 am

Post by SoreE yes »

Hi, yes I saw that 3rd result from Google, but check out the following, which appears to contradict it, and which is where I got the php code from.

http://www.blue105.com/internet/support ... fline.html.

Regards, Mike

Jcart | I've corrected link
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

That link didn't work for me... I got non-loading images and no relevant content :-(

Did you try the example code on the Google result page I quoted?

Cheers,
Kieran
Post Reply