importing a mysql database with PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Leb_CRX
Forum Newbie
Posts: 9
Joined: Thu May 18, 2006 8:27 am

importing a mysql database with PHP

Post 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
nshiell
Forum Newbie
Posts: 12
Joined: Mon Oct 19, 2009 12:20 pm

Re: importing a mysql database with PHP

Post 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)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: importing a mysql database with PHP

Post 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
Leb_CRX
Forum Newbie
Posts: 9
Joined: Thu May 18, 2006 8:27 am

Re: importing a mysql database with PHP

Post 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.
Leb_CRX
Forum Newbie
Posts: 9
Joined: Thu May 18, 2006 8:27 am

Re: importing a mysql database with PHP

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: importing a mysql database with PHP

Post 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());
Post Reply