Best way to import a MySQL Dump file

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

AlexC
Forum Commoner
Posts: 83
Joined: Mon May 22, 2006 10:03 am

Best way to import a MySQL Dump file

Post by AlexC »

Hey,

I'm coding the "Install" script for my OpenSource CMS and it's going quite well. Currently I'm coding the part that creates the MySQL Databases and adds the default data to them.

I'm not sure on how to do this, at first I though I could just do:

Code: Select all

$SQLqueries = explode( ";", file_get_contents( 'install/stage3/tables.sql' ) );

foreach( $SQLQueries as $key ) {
$Query = mysql_query( $key );
}
But them problem with this is that if any data contain in the mysql has a ";" then the SQL query will get cut in half ( For example & ) So this didn't work at all.

I have no Idea on how to do this, I spent like an hour in ##php but no one could really help me out. I also tried using exec() but I couldn't get that to work either =\

Thanks,
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You know, you might want to have a look at phpMyAdmin and see how they handle their SQL script loads.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

In MySQLi there is this.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Probably not the best way, but it works...

-Set autocommmit, unique_checks, foreign_key_checks to 0..
- And then load the data...
- then set foreign_key_checks and unique_checks to 1...
- source ./path/to/file
- commit
- set autocommit to 1...

But since i'm lazy i wrote a little bash script that does all this for you ;)

http://timvw.madoka.be/programming/bash/myrestore.txt
AlexC
Forum Commoner
Posts: 83
Joined: Mon May 22, 2006 10:03 am

Post by AlexC »

You know, you might want to have a look at phpMyAdmin and see how they handle their SQL script loads.
Hum I tried doing that, but it's like looking at another language - I can't understand it!

Thanks for the script, timvw - but I don't see how I can include that into my PHP as it's a bash script?

I'll check out that MySQLi Ole, thanks

Is there another way then to store the queries instead of a SQL Dump? Maybe I could do some kind of XML document
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

AlexC,
Just copy the bash source file like 'restore.sh' and make it executable by chdmod 755 . Pass the arguements through PHP and use system function to execute that bash file.


Cheers,
Dibyendra
AlexC
Forum Commoner
Posts: 83
Joined: Mon May 22, 2006 10:03 am

Post by AlexC »

But this would only work on Linux right? I need my CMS to be portable to other OS's as much as possible
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

This script depends on the fact that there is a mysql-client program available in the path.. This is an assumption that can be made true for most platforms :)

You can build the same string with php... And then call the mysql-client program from your php script...

Code: Select all

<?php
// build string
$query = 'SET AUTOCOMMIT = 0;';
$query .= .....;

// run command
`$query`;
?>
screevo
Forum Commoner
Posts: 25
Joined: Fri Aug 25, 2006 12:04 am

Post by screevo »

AlexC_ wrote:
You know, you might want to have a look at phpMyAdmin and see how they handle their SQL script loads.
Hum I tried doing that, but it's like looking at another language - I can't understand it!

Thanks for the script, timvw - but I don't see how I can include that into my PHP as it's a bash script?
You might want to look into the exec() function. I believe that would let you run the bash script. Just place the bash script where you want it and do

Code: Select all

<?
exec(sh /path/to/file/script arguments)
?>
Just make sure that you make damn sure you know EXACTLY what you are exec'ing.

Some hosts actually won't allow Exec because it can do a lot of damage.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Code: Select all

exec(sh /path/to/file/script arguments)
Quotes?
Semi-colon?
Backtick instead of exec?
screevo
Forum Commoner
Posts: 25
Joined: Fri Aug 25, 2006 12:04 am

Post by screevo »

ole wrote:

Code: Select all

exec(sh /path/to/file/script arguments)
Quotes?
Semi-colon?
Backtick?
I've never used the command as it's disabled on my server. The backtick is interesting though. I didn't know about that.

SM
AlexC
Forum Commoner
Posts: 83
Joined: Mon May 22, 2006 10:03 am

Post by AlexC »

hum if exec() doesn't run on all php configs then I can't use it. I think the best way would be an xml document.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

In that case you can't use php... (Since php5 the mysql extension isn't enabled by default.. So it would be impossible to talk with a mysql dbms.. Might want to rethink your policy ;))

<Off-topic>What do you mean with 'XML would be the best way'? And what would be the dtd/xsd of the xml file?
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

In that case you can't use php... (Since php5 the mysql extension isn't enabled by default.. So it would be impossible to talk with a mysql dbms.. Might want to rethink your policy Wink)
MySQL is nearly always enabled but excution functions are quite often disabled and are also subject to other restrictions and of course the OS. The policy is fine.

AlexC_ do you have control of install/stage3/tables.sql, if you do why not change it to install/stage3/table.sql.php and put each separate query as a separate element of an array?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

How about looking at phpBB's installation code then. phpMyAdmin is a bit rough, but phpBB code might be a little easier to read through.
Post Reply