dumb sql question

juschillinnow

I used phpMyAdmin to export an .sql file of a database and structure. How can I use php to make the database again?

I tried saving it as a text file and then using the file function to read it into a string and then running that as an sql query.

Doesn't work.

Any ideas?

nnichols

I suggest you use phpMyAdmin to read it back in or if you have access to the command line mysql client read the file back in with -

mysql -hhost -uuser -ppass < file.sql

To do it with phpMyAdmin you need to go to the screen that allows you to enter queries. Somewhere on that page there is a file upload box.

Hope this helps 😉

juschillinnow

well not really. I know that I can put it back in with phpMyAdmin. But I want to be able to create this database on other computers with php/mysql so I wanted a way to write something where I can "run" the .sql file and make the database structure

juschillinnow

this is what I am trying to do:

include("dbconnect.php");
$file = join("\n", file("./install/db.php"));
$result = mysql_query($file, $mysql_link);

// I just saved the .sql file as a db.php. I also tried db.txt too.

nnichols

In that case I suggest you have a look at the way it is done in the code for phpMyAdmin. It is quite complex.

Do you have command line access to the computers you want to put the code on?

One thing you could try - never tried this method myself - is to use the passthru method to execute the command I suggested above -

passthru("mysql -uuser -ppass < file.sql")

having already uploaded the file to the server.

juschillinnow

now there's enginuity 🙂 That's what I was looking for. Not sure if it works yet.

If it did work. Do you suppose it would work on both UNIX and Windows OS's?

nnichols

Not sure. I'm just doing a little background reading 🙂

diego25

It should, as long as both systems have the mysql client installed, and that PHP has access to that program.

Diego

nnichols

One other method you could try is to read the file in to a variable and then use explode() to split into multiple queries using ";\n" as the delimiter.

$filename = 'blah.sql';
$fd = fopen($filename, "r");
$contents = fread($fd, filesize($filename));

$sql_array = explode(";\n", $contents);

$i = 0;

while($i < count($sql_array)) {
$sql = $sql_array[$i];
mysql_query($sql);
$i++;
}

Again, I haven't tried this but it is probably worth having a play and seeing how you get on.

Let me know how you get on 😉

juschillinnow

I'm still looking into it. While I'm doing that.....do I have any other options?

I will probably have some pretty dumb people using this and I don't want them to have to install something else if they don't already have it. (mysql client).

Sorry. Jumped the gun in posting this one 🙂

BTW is the php server slow or is that just my computer?

nnichols

Try the method above. Normally if I am writing something that needs to be installed by "joe public", I write an install script instead of doing it from a dump file. Boring task, but it's safe.

juschillinnow

you're da man!