Page 1 of 2

Best way to import a MySQL Dump file

Posted: Sat Aug 26, 2006 1:07 pm
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,

Posted: Sat Aug 26, 2006 1:13 pm
by RobertGonzalez
You know, you might want to have a look at phpMyAdmin and see how they handle their SQL script loads.

Posted: Sat Aug 26, 2006 5:07 pm
by Ollie Saunders
In MySQLi there is this.

Posted: Sat Aug 26, 2006 6:02 pm
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

Posted: Sun Aug 27, 2006 3:44 am
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

Posted: Sun Aug 27, 2006 5:28 am
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

Posted: Sun Aug 27, 2006 5:31 am
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

Posted: Sun Aug 27, 2006 6:04 am
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`;
?>

Posted: Sun Aug 27, 2006 7:04 am
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.

Posted: Sun Aug 27, 2006 9:14 am
by Ollie Saunders

Code: Select all

exec(sh /path/to/file/script arguments)
Quotes?
Semi-colon?
Backtick instead of exec?

Posted: Sun Aug 27, 2006 9:22 am
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

Posted: Sun Aug 27, 2006 11:55 am
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.

Posted: Sun Aug 27, 2006 5:36 pm
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?

Posted: Sun Aug 27, 2006 5:49 pm
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?

Posted: Sun Aug 27, 2006 7:35 pm
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.