Page 1 of 2
How to use php to execute sql
Posted: Mon Oct 10, 2005 5:49 pm
by Antek_Ryu
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
Posted: Mon Oct 10, 2005 11:46 pm
by wyred
Posted: Tue Oct 11, 2005 4:15 am
by Antek_Ryu
I already have this installed I want to do something similar but through my web page and I am using php5
Antek
Posted: Tue Oct 11, 2005 5:07 am
by raghavan20
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.sql
Posted: Tue Oct 11, 2005 5:22 am
by Antek_Ryu
Hi
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
Posted: Tue Oct 11, 2005 6:31 am
by Jenk
Code: Select all
mysql_query(file_get_contents('/path/to/file.sql')) or die("DB Error!");
Or use
shell_exec()
Posted: Tue Oct 11, 2005 7:41 am
by Antek_Ryu
Thanks for that jenk
Just one more question how do i know if my commands have been executed successfully. The file is dropping all existing tables then creates them and then inserts the values.
Many thanks
Posted: Tue Oct 11, 2005 7:50 am
by Joe
If there is an error with the execution then you will be resulted in an error from the mysql_error() function.
Posted: Tue Oct 11, 2005 8:21 am
by Antek_Ryu
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
Code: Select all
mysqldump -uroot -ppass21 --databases Customers > database.sql
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?
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();
}
Posted: Tue Oct 11, 2005 8:35 am
by feyd
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..
Posted: Tue Oct 11, 2005 8:51 am
by Antek_Ryu
fantastic feyd, many many thanks. It works!!!
can you send me in the right direction as how i can check check the success/failure of my queries.
once again thanks.
Antek
Posted: Tue Oct 11, 2005 8:53 am
by RobertGonzalez
You ever thought of downloading phpMyAdmin and looking at the way it handles database inserts from a file? It handles multiple executions from one file pretty well. Look at their code.
Posted: Tue Oct 11, 2005 9:08 am
by Antek_Ryu
Everah thanks for the suggestion, I have had a look it seemed to be very complex for me to understand at present. So i decided to do it myself in a simpler manner. Hopefully my php skills are getting better from a newbie to intermediate soon.
Antek
Posted: Tue Oct 11, 2005 10:54 am
by Antek_Ryu
Hi
I am still having a problem in determining what query has been executed can someone point me in the right direction
Thanks
Posted: Tue Oct 11, 2005 1:22 pm
by RobertGonzalez
Can you post the content of your SQL file. Maybe if we see what it looks like we can figure out how to chop it and submit each query one by one.