run sql query from a string using php

Question

Possible Duplicate:
execute sql query from sql file

i have a file named file.sql which is exported from a database 'desktop'. i want import this old database to my new database 'laptop' using php scripts. i opened the file.sql using fopen and read all contents using fread and stored whole content to a string named $sqldata. how can i execute whole query from that variable??

Answer 1

Hi,

In this kind of situation, I would rather use the mysql command-line utility to import the dump as a whole.

For instance, something like this should do :

mysql --user=YOUR_LOGIN --password=YOUR_PASSWORD --host=localhost YOUR_NEW_DATABASE_NAME < your_dump.sql

If you do not have command-line access to your server, maybe you can run this with exec / shell_exec / ...

If you really cannot use the mysql command from the command-line (nor using exec or any equivalent), maybe you can try using mysqli_multi_query ; as stated in the manual :

Executes one or multiple queries which are concatenated by a semicolon.

Considering your dump prbably only contains several queries, separated by semi-colons, this might actually work...

Edit : as other people pointed out, what about reading the answers that have been given to your other questions ? For instance, there is some interesting stuff here : execute sql query from sql file

If there is something you don't understand in those answers, you can post comments to them, to, hoppefully, get more precisions.

Answer 2

You have to split the string which effectively contains multiple queries into a list of single queries (which is not as simple as splitting by ';') and execute them separately or you can simple use the MySQL Improved Extension which is, as far as I know, the only MySQL PHP extension that natively supports multiple queries which are concatenated by a semicolon by its mysqli::multi_query() / mysqli_multi_query() method/function.

By the way - using the MySQL command line client will be the best solution to this problem as Pascal MARTIN suggested.

Answer 3

I might be missing something, but there is no single function for such a batch-query in PHP. So, other than using the mysql command line utility, you will have to split file.sql into seperate statements and execute them one after the oter. The easiest way to do this might be to use preg_split, altough I'd like to point out that this might fail under certain cercumstances.

Update: Ok, as I just learned from S. Gehrig's answer, there is such a function in PHP.

Answer 4

Someone just asked a similar question

And my answer is the same:

<?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("file.sql");

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

This because mysqli can accept multiple query strings using the function 'mysqli_multi_query'.

Hope this helps.

Answer 5

MySQL GUI Tools sounds like what you need. Using the Administrator you can backup and restore databases. I suggested this since your moving your DB to your laptop.