How to use php to execute sql
Moderator: General Moderators
How to use php to execute sql
Hi All
I have a mysql database which I have backed up into a file using the following command below.
mysqldump -uuserabc -phelloworld database123 > db011005.sql
Now using php how can I restore my system to the file I have created above. Example I have the file located on /home/userabc/db/db011005.sql. I have created a web page which says restore corrupted system, the user clicks on it and a php script executes the db011005.sql file as specified. I have 6 tables that need to be restored I need to be sure that the transaction restores all of them one by one (the databases are not that big!).
Many thanks for your help.
Regards
Antek
I have a mysql database which I have backed up into a file using the following command below.
mysqldump -uuserabc -phelloworld database123 > db011005.sql
Now using php how can I restore my system to the file I have created above. Example I have the file located on /home/userabc/db/db011005.sql. I have created a web page which says restore corrupted system, the user clicks on it and a php script executes the db011005.sql file as specified. I have 6 tables that need to be restored I need to be sure that the transaction restores all of them one by one (the databases are not that big!).
Many thanks for your help.
Regards
Antek
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
may be this shd help
Code: Select all
mysql your_db_name < yourpath/file_name.sql
or
use your_db_name
mysql < your_path/file_name.sqlHi
Thanks for the reply but I do not know how I can execute that command via php script. I think the above command is one that you execute within the operating system shell.
I have the txt file for the database i want to restore. The text file will has commands to drop all the tables and then rebuild them, i just dont know how to do this via my web page thru sql
Antek
Thanks for the reply but I do not know how I can execute that command via php script. I think the above command is one that you execute within the operating system shell.
I have the txt file for the database i want to restore. The text file will has commands to drop all the tables and then rebuild them, i just dont know how to do this via my web page thru sql
Antek
Code: Select all
mysql_query(file_get_contents('/path/to/file.sql')) or die("DB Error!");Hi guys thanks again, sorry to be a pain.
This is my php code below when I execute this code in my php webpage the webpage shows no errors. I then take alook at my database and nothing has happened.
One more observation is that the database.sql file was created using the command
This file has given me many lines that have comment like below. Will this mess up my sql command in the php code right below?
This is my php code below when I execute this code in my php webpage the webpage shows no errors. I then take alook at my database and nothing has happened.
One more observation is that the database.sql file was created using the command
Code: Select all
mysqldump -uroot -ppass21 --databases Customers > database.sqlThis file has given me many lines that have comment like below. Will this mess up my sql command in the php code right below?
Code: Select all
/*!40000 ALTER TABLE `tbl_customers` DISABLE KEYS */;Code: Select all
public function doRestore(){
$dbLink = new mysqli($this->dbHost, $this->dbUser, $this->dbPass, $this->dbName);
if(!$dbLink) die("unable to connect to database" . mysqli_connect_errno());
$query = (file_get_contents('c:/database.sql'));
$result = $dbLink->query($query);
$result->free();
$dbLink->close();
}- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
I believe mysqli_query() (what you are using in the code just posted) only supports execution of one query. You may want to use mysqli_multi_query() unless your dump is one query (extremely doubtful.)
It would probably be a good idea to have your code check the success/failure rate of your queries too..
It would probably be a good idea to have your code check the success/failure rate of your queries too..
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA