creating table from .sql file

geoffs

i have a .sql table structure that I exported from myadmin and on command (in a php script) i would like to have the php script execute the .sql file...basically to drop (if exists) then recreate the table...what is the best way to accomplish this?

hrosas

Well in your sql file you can start wirh
drop table if exists table_name;
and then the table definition
create table table_name ( blah blah );

then you can read the content of the file into a variable then just do a mysql_query ($your_var ) and that's all.

Hector

www.oksonora.com OKSonora

perpetualshaun

What geoffs has described is EXACTLY what I'm trying to do, and I don't quite understand the response here.

I have a .sql file that has SEVERAL tables in it, and I basically want to write a PHP script that will read and execute all the queries in this file to restore all the defaults for this app I'm working on.

How do I read in the file as a variable and execute?

Thanks,

Shaun

Sxooter

If it's not real big, you can use the file() construct:

// connect to your db somewhere up here.
$filename= "/path/to/file";
$lines = file($filename);
foreach ($lines as $l){
    $res = mysql_query($l);
}

p.ss you might want to add some error checking there somewhere... 🙂

tunage

You could use the exec command to dump data into a database as you would via the mysql command line.

eg, from the command prompt -

c:\web\mysql\bin>mysql -u root -ppw mydb<D:\dump\mydump.sql

can be executed in php -

exec("C:\\web\\mysql\\bin\\mysql -u root -ppw mydb<D:\\dump\\mydump.sql");

Just change the path to mysql install and where the dump file is.

perpetualshaun

Hi tunage,

I like that solution! I'm trying to get it to work here on my local machine before I change the paths to make it work on the web server and all. I'm surprised this isn't working:

exec("C:\apache\mysql\bin\mysql -u root -ppw mydb<C:\apache\htdocs\diversifiedcomputer.net\pp\table_defaults.sql") OR die ("Uh... this didn't work");

Any ideas? I think I'm following the format that you laid out exactly, but it's not working for me.

tunage

If you run the command through the mysql command prompt what happens, do you get any errors?

c:\apache\mysql\bin>mysql -u root -ppw mydb<C:\apache\htdocs\diversifiedcomputer.net\pp\table_defaults.sql

Also, its not good practice to be using the root account for this, so the account you do use you needs to have the required privileges to do whats in the dump file (eg drop/create/insert/update etc)