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