How to use php to execute sql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Antek_Ryu
Forum Commoner
Posts: 34
Joined: Tue Aug 09, 2005 10:55 am

How to use php to execute sql

Post 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
wyred
Forum Commoner
Posts: 86
Joined: Mon Dec 20, 2004 1:59 am
Location: Singapore

Post by wyred »

Antek_Ryu
Forum Commoner
Posts: 34
Joined: Tue Aug 09, 2005 10:55 am

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
Antek_Ryu
Forum Commoner
Posts: 34
Joined: Tue Aug 09, 2005 10:55 am

Post 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
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

mysql_query(file_get_contents('/path/to/file.sql')) or die("DB Error!");
Or use shell_exec()
Antek_Ryu
Forum Commoner
Posts: 34
Joined: Tue Aug 09, 2005 10:55 am

Post 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
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

If there is an error with the execution then you will be resulted in an error from the mysql_error() function.
Antek_Ryu
Forum Commoner
Posts: 34
Joined: Tue Aug 09, 2005 10:55 am

Post 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();
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
Antek_Ryu
Forum Commoner
Posts: 34
Joined: Tue Aug 09, 2005 10:55 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Antek_Ryu
Forum Commoner
Posts: 34
Joined: Tue Aug 09, 2005 10:55 am

Post 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
Antek_Ryu
Forum Commoner
Posts: 34
Joined: Tue Aug 09, 2005 10:55 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply