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?

    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

      2 years later

      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

        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... 🙂

          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.

            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.

              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)

                Write a Reply...