CODEWITHSUNDEEP
php
anupam
anupam

Reputation:

Execute sql query from sql file

I want to include all tables from a database "shop" to mydatabase newshop. I export that "shop" database, and now it is named as shop.sql. Is there any way to execute whole query from that file instead of importing directly to database newshop.

Upvotes: 1

Views: 19846

Answers (4)

NawaMan
NawaMan

Reputation: 25687

I think what you need might be:

$ mysql -u $USERNAME -p < "$SQLFILENAME"

Where $USERNAME is the user name of mysql like root and $SQLFILENAME is the name of the sql file ('shop.sql' in this case).

The above command will ask you to type in password. If you don't want to do that every time, you can use '--password=$PASSWORD' BUT be careful. This password may be saved in history and it might be in the wrong hand (If you run it from PHP, it may not be a problem).

OR you can use mysqli to run it.

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = file_get_contents("shop.sql");

/* execute multi query */
if (mysqli_multi_query($link, $query))
     echo "Success";
else 
     echo "Fail";
?>

Hope this helps.

Upvotes: 9

Graviton
Graviton

Reputation: 83306

Here's the code you might be interested in:

<?php

  $dbhost = 'localhost';
  $dbuser = 'root';
  $dbpass = 'password';
  $db = 'newshop';
  $file =dirname(__FILE__).'\\'.'shop.sql';
  $mySQLDir='"C:\\Program Files\\MySQL\\MySQL Server 5.1\\bin\\mysql.exe"';



    if ($dbpass != '') {
        $cmd = $mySQLDir.' -h '.$dbhost.' --user='.$dbuser.' --password='.$dbpass.' < "'.$file.'"';

    } else {
        $cmd = $mySQLDir.' -h '.$dbhost.' --user='.$dbuser.' < "'.$file.'"';
    }

     echo $cmd;  

   exec('"'.$cmd.'"',$out ,$retval);
   echo "\n";
    echo ($retval);

?>

Upvotes: 0

brianreavis
brianreavis

Reputation: 11546

Considering this question is tagged with PHP, here's how you could do it:

mysql_query('USE newshop');
$queries = file('shop.sql');
while(list($i, $query) = each($queries)){
    $query = trim($query, ';');
    if(!empty($query) && $query != 'USE shop'){
        @mysql_query($query);
    }
}

Make sure "USE shop" in the if statement matches whatever syntax is used in the SQL file. For instance, you might need to make it USE 'shop' or USE schemaname.shop. Or, if there's no USE command in there at all, you can just take that condition right out.

Upvotes: 2

meder omuraliev
meder omuraliev

Reputation: 186762

>>> mysql -u username -p
>>> show databases;
>>> use foo;
>>> source /home/mannu/file.sql

I believe that's what you're looking for? Or did I misunderstand?

Upvotes: 1

Related Questions