September 1st, 2003, 04:05 AM
php script for mysql import
i have a csv file local to my pc, i need to import this comma delimited csv automatically to my mysql database on my web server,
i now think??? i need a php script that i can run as a cron job on my web server, this would automate the process and i could have a refresh button on the website to refresh the page automatically
i thought i could schedule a ftp program to upload the csv file to the web server
but there's a problem,, iam no good at php, can any help me????
September 1st, 2003, 04:59 AM
Moved to Scripts forum from PHP
September 1st, 2003, 01:41 PM
How much help do you require?
Have you got a PHP script that is connecting to your MySQL database, or do you want it all?
September 2nd, 2003, 02:35 AM
September 2nd, 2003, 04:24 AM
Re: php script for mysql import
Okay, here's a starter.
Firstly, I suggest reading the following articals:
PHP 'MySQL Functions': http://uk.php.net/manual/en/ref.mysql.php
MySQL 'Load Data': http://www.mysql.com/doc/en/LOAD_DATA.html
I'll give you a short script so you're started.
Call the file something like 'update.php'.
This is tested and working on my system. So, hopefully, it'll work on yours
## Connect to a local database server (or die) ##
$dbH = mysql_connect('localhost', 'user', 'pass') or die('Could not connect to MySQL server.<br>' . mysql_error());
## Select the database to insert to ##
mysql_select_db('test') or die('Could not select database.<br>' . mysql_error();
## CSV file to read in ##
$CSVFile = 'data.txt';
mysql_query('LOAD DATA LOCAL INFILE "data.txt" INTO TABLE News FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error());
## Close database connection when finished ##
Obviously, you'll have to change the server username and password and the database name for it to work.
Hope it helps
November 20th, 2003, 11:24 AM
Is there a way of importing a text file that has the likes of...
as an entry for a field where fields are seperated by , and enclosed by ' but any characters ' are escaped by / as above.
I've tried modifying the original posted here as in...
..but I keep getting...
mysql_query('LOAD DATA LOCAL INFILE "test" INTO TABLE "t1" FIELDS TERMINATED BY "," ENCLOSED BY "'" ESCAPED BY "\\" LINES TERMINATED BY "\r\n";') or die('Error loading data file.<br>' . mysql_error());
Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING
I've also tried ....
..but I keep get...
mysql_query('LOAD DATA LOCAL INFILE "test" INTO TABLE "t1" FIELDS TERMINATED BY "," ENCLOSED BY "\'" ESCAPED BY "\\" LINES TERMINATED BY "\r\n";') or die('Error loading data file.<br>' . mysql_error());
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 '"mms3" FIELDS TERMINATED BY "," ENCLOSED BY "'" ESCAPED BY "\"
Is there a way for dealing with the ' in the ENCLOSED BY " " part so that the statement doesn't break up and get parsed wrongly?
I've tried phmyadmin 2.5.4-rc1 but getting " You should select file which you want to insert.", which I think is being caused by the above parsing problem.
Any help is greatly appreciated as I've tried various things but no luck.
p.s. Probably should have put this post in the PHP forum!
Last edited by fluidicity; November 20th, 2003 at 11:58 AM.
April 23rd, 2005, 04:45 AM
I'm new in php scripting and i need exatly the same script that u posted. Could u give me some more detail about runing the script? Where do i need to plase the php script? On my webserver? (it is a linux) The csv file is on my windows machine.
Originally Posted by Wite_Noiz
June 17th, 2005, 04:50 PM
I was trying something, and your code really helped me, thanks Wite_Noiz !!
January 22nd, 2010, 09:09 PM
I was trying your script, but i don't understand how take data from local. Becouse error , file not found..
Please answer my question....