Load data from file

[buzzt]

I need to be able to load the data from a CSV file from my local machine into my MySQL database on the remote server by way of a php page. I have seen some code, but nothing that really works well.

Any ideas?

 

[skiflyer]

Checkout phpmyadmin at

http://www.phpmyadmin.net

You can do it through their, or if you need to setup your own page, you can read the SQL they use to do it and make your own script around that.

-Rob

 

[sleipnir214]

Once you have accepted the CSV file and written it to the filesystem (

http://www.php.net/manual/en/features.file-upload.php),

there are two ways of doing it.

One is reading the file in through PHP, constructing a SQL statement from the values in the file, then executing each constructed SQL query in turn.

Another way is to use a single query containing MySQL's LOAD DATA INFILE command (

http://www.mysql.com/doc/en/LOAD_DATA.html)

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[buzzt]

Here's what I did...

I created a form to upload the data
<form action=&quot;import.php&quot; method=&quot;post&quot; enctype=&quot;multipart/form-data&quot;>
<input name=&quot;file&quot; type=&quot;file&quot; size=&quot;15&quot;>
<input type=&quot;submit&quot; value=&quot;Submit&quot;>
</form>

...and here is the import.php (the relevent part)
$result = mysql_query(&quot;LOAD DATA LOCAL INFILE '$file' INTO TABLE schedule&quot or die(mysql_error());

I keep getting the same error I did with other scripts:
File 'chpuploadtempphp24.tmp' not found (Errcode: 2)

I'm on a win2k server (if that helps).

 

[sleipnir214]

To where is PHP uploading your files? Is that path and filename correct?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[buzzt]

What I would really like to do is something similar to what PHPMyAdmin does. Just read the data from the (local) file and insert it into the database. Is that not possible?

 

[sleipnir214]

Sure. Use my first suggestion.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[buzzt]

But all that will do is upload the file. How will I tell MySQL to import the data from it at the same time?

 

[sleipnir214]

That would be the part of my earlier post which reads: One is reading the file in through PHP, constructing a SQL statement from the values in the file, then executing each constructed SQL query in turn.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[buzzt]

So PHP will actually break down the file and then insert the data. Is that what you mean?

 

[sleipnir214]

So you will program PHP to break down the file and then insert the data, yes.

I still recommend using LOAD DATA INFILE. It sounds to me that figuring out a path or permissions problem with the temporary file would be easier than writing the code to create umpteen SQL statements.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[buzzt]

That's what I was just thinking.

Thanks.

 

[sleipnir214]

So we're back to my earlier question:

To where is PHP uploading your files? Is that path and filename correct?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[buzzt]

Do you mean the value specified in the php.ini file?

 

[sleipnir214]

The directory would most likely be found in php.ini as the value for the configuration setting upload_tmp_dir.

However, PHP, in the online manual section on handling file uploads (

http://www.php.net/manual/en/features.file-upload.php)

relates that the superglobal predefined array $_FILES will have all the information necessary to find the file on the filesystem.

The array element $_FILES['[the form element name]'][tmp_name] contains a full path and filename.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[sleipnir214]

ADDENDUM:
I just don't know where the value of $file in your example comes from.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!