Page 1 of 1

importing a mysql database with PHP

Posted: Mon Oct 19, 2009 1:11 pm
by Leb_CRX
I am transferring a mysqldump of a database between servers, and I need the server to be able to load it...the transfer goes fine, the loading is where I'm running into the issue

I've done it before using the system command, but since safe mode is turned on and I don't have access to php.ini, it won't work.
I tried importing it by reading the .sql file, exploding the file at the semi-colon, but that doesn't work either since the actual content of the file contains quite a bit of that

does anyone have any ideas for me? I am totally out of ideas and just spinning my wheels

Re: importing a mysql database with PHP

Posted: Mon Oct 19, 2009 1:20 pm
by nshiell
If you have command line access to the server have u done?

Code: Select all

mysql -uroot -p db_name < /path/to/file.sql
That will run the query on the database (don't forget to back up what you have already)

Re: importing a mysql database with PHP

Posted: Mon Oct 19, 2009 1:28 pm
by califdon
You can use PHP to run a LOAD DATA INFILE LOCAL query. Read http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Re: importing a mysql database with PHP

Posted: Tue Oct 20, 2009 9:36 am
by Leb_CRX
nshiell wrote:If you have command line access to the server have u done?

Code: Select all

mysql -uroot -p db_name < /path/to/file.sql
That will run the query on the database (don't forget to back up what you have already)
Thanks for the reply, but I need this to be run by a script...it will have files uploaded frequently, and I would like to call a script to load each one.

it's the actual loading through PHP that's causing the issue.

Re: importing a mysql database with PHP

Posted: Tue Oct 20, 2009 9:42 am
by Leb_CRX
califdon wrote:You can use PHP to run a LOAD DATA INFILE LOCAL query. Read http://dev.mysql.com/doc/refman/5.0/en/load-data.html
hey, I checked it out and tried it a few things, no go

if you happen to have a link to a tutorial of using it handy, I'd really appreciate it...I couldn't seem to find anything concrete searching on google...most of it was no good

here's what I got so far:

Code: Select all

$conn = mysql_connect($dbhost, $dbuser, $dbpasswd) or die ('Error connecting to mysql');    
mysql_select_db($dbname) or die(mysql_error());
 
mysql_query("LOAD LOCAL DATA INFILE '$filename' INTO TABLE `index_urls` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';",$conn);
 
I got error reporting, no errors are coming up, but it's not actual loading the table properly.

and for the dump, I am just doing

mysqldump -u root -ppasswordhere index_urls > index_urls.sql

using the command, should I add any switches?

Re: importing a mysql database with PHP

Posted: Tue Oct 20, 2009 12:18 pm
by califdon
Instead of

Code: Select all

mysql_query("LOAD LOCAL DATA INFILE '$filename' INTO TABLE `index_urls` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';",$conn);
I would suggest

Code: Select all

$sql="LOAD LOCAL DATA INFILE '$filename' INTO TABLE `index_urls` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";
mysql_query($sql) or die(mysql_error());