MySQL replace database

Question

I have a .sql file and want to replace the already existing database by clicking a button. Everything works fine. Except the create query. Is there any query or command to import whole databases?

    $filename = 'file.sql';
    // MySQL host
    $mysql_host = 'localhost';
    // MySQL username
    $mysql_username = 'user';
    // MySQL password
    $mysql_password = 'pw';
    // Database name
    $mysql_database = 'dbName';

    // Connect to MySQL server
    mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
    // Select database
    mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());

    $drop_db = "DROP DATABASE dbName";

    mysql_query($drop_db) or die ("error");

    $create_db = "";

Possible duplicate of [How can I import a database with MySQL from terminal?](http://stackoverflow.com/questions/4546778/how-can-i-import-a-database-with-mysql-from-terminal) — Sergio Ivanuzzo, Nov 18 '15 at 10:07
No, there is no such single query or command. You need to issue a series of commands to create a database, to create each table, view, trigger, etc. within the database, and to populate data. There are tools that help you to migrate databases in one way or the other, but your question lacks details, so it is difficult to recommend such tool. However, with mysqldump or phpmyadmin tools you can create such a dump of a database that contains all necessary sql commands to recreate a database by running those commands. — Shadow, Nov 18 '15 at 10:15

Professor Abronsius · Answer 1 · 2015-11-18T10:50:50.257

1

You could try something like:

$cmds=array_filter( file( $filename ) );
foreach( $cmds as $cmd ){
    echo $cmd;
}

If that looks ok substitute the echo for mysql_query( $cmd )... totally untested btw.

    $sql=array();
    $sourcefile='C:\data\db_20101222_0957.sql';

    $cmds = file( $sourcefile, FILE_SKIP_EMPTY_LINES | FILE_IGNORE_NEW_LINES );
    foreach( $cmds as $cmd ){

        if ( substr( $cmd, 0, 2) == '--' || $cmd == '' || substr( $cmd,0, 2)=='/*' ) continue;

        $sql[]=$cmd;

        if ( substr( trim( $cmd ), -1, 1 ) == ';' ){
            /* Query */
            $query=implode( PHP_EOL, $sql );
            /* Execute query */

            echo '<pre>',$query,'</pre>';

            $sql=array();
        }
    }

edited Nov 18 '15 at 10:50

answered Nov 18 '15 at 10:14

Professor Abronsius

33,063
5
32
46

Giving that some more thought ( I need to think more often ) there are likely to be issues ~ if each of the statements within your sql file are on a single line then it will probably be ok, bbut if each statement occupies multiple lines you would need to use some other code to ensure that you identify where each statement begins and ends. – Professor Abronsius Nov 18 '15 at 10:30
some do and some don't. I think it's easier to replace the tables in the database isn't it? – Valcone Nov 18 '15 at 10:32
yes, definitely - I have tried this sort of thing before and found it frustrating ~ though you may wish to have a look at this if you have not alread yseen it. http://stackoverflow.com/questions/19751354/how-to-import-sql-file-in-mysql-database-using-php – Professor Abronsius Nov 18 '15 at 10:40
Ok i think i will try that! I keep you up to date if it worked. :D – Valcone Nov 18 '15 at 10:41

score 1 · Answer 2 · answered Nov 18 '15 at 10:50

I got it. The solution is to drop the tables not the whole database.

function resetClient() {
    $erg = false;
    try {
        // get all tablenames
        $sql = "SHOW TABLES FROM dbName";
        $res = $this->conn->query($sql);

        $this->conn->query("SET FOREIGN_KEY_CHECKS=0");

        // drop all tables in db
        if (is_object($res)) {
            if (($res->num_rows > 0)) {
                while ($row = $res->fetch_row()) {
                    $this->conn->query ("DROP TABLE " . $row[0]);
                }
            }
        }

        $this->conn->query("SET FOREIGN_KEY_CHECKS=1");

        //pause
        time_nanosleep(0, 250000000);

        // create tables from script
        $sql = file_get_contents('./scripts/file.sql');
        $this->conn->multi_query($sql);

        $erg = true;

        error_log(date("Y-m-d H:i:s")." - DB resetted\n", 3,
            "./scripts/success.log");
    } catch (Exception $e) {
        // log
        error_log(date("Y-m-d H:i:s")." - DB error\n"
            . "resetClientDB() \n"
            . "Reset error \n"
            . $e->getMessage() . "\n" , 3,
            "./scripts/error.log");
    }
    return $erg;
}

MySQL replace database

2 Answers2