CODEWITHSUNDEEP
phpmysqlsqlpdomysqli
Adam
Adam

Reputation: 2031

Wait for mysqli::multi_query to finish before executing next statement

I have some code that uses Mysqli::multi_query to drop and rebuild a development database from a flat .sql file. After rebuilding the mysqli connection is closed and the code then reconnects to that DB with PDO to run queries against it.

/* Drop and Rebuild DB using mysqli::multi_query */
$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_errno) {
    die('Failed to connect to MySQL: (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

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

if (!$mysqli->multi_query($query))
    die("Failed to refresh db: $this->target_db");

$mysqli->close();

/* Execute queries against rebuilt database using PDO */
try {
    $sth = new PDO("mysql:host=$host;dbname=$db", $user, $ass);
    $test1 = $sth->query("SELECT * FROM tbl");
    var_dump($sth->errorInfo());
    $test2 = $sth->query("SHOW COLUMNS FROM tbl");
    var_dump($sth->errorInfo());
}
catch(PDOException $e) {
    die($e->getMessage());
}

Output from 1st var_dump ($test1):

array(3) {
  [0]=>string(5) "00000"
  [1]=>NULL
  [2]=>NULL
}
object(PDOStatement)#3 (1) {
  ["queryString"]=>string(31) "SELECT * FROM tbl"
}

Output from 2nd var_dump ($test2):

array(3) {
   [0]=>string(5) "42S02"
   [1]=>int(1146)
   [2]=>string(45) "Table 'db.tbl' doesn't exist"
}
bool(false)

Why does the query from $test2 fail? If I remove the mysqli code bock then everything works perfectly. Both queries are running against the same tbl in the same db. Is there something going on that is asynchronous and if so how do I force my script to wait for the mysqli::multi_query to finish executing before instantiating PDO?

Upvotes: 1

Views: 4383

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94662

I dont know this for a fact but I am assuming that the connection is not actually being closed when you run $mysqli->close(); because there are X resultset still unprocessed or free'd.

You could try this to see if it clears the issue :-

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

if ($mysqli->multi_query($query)) {
    do {
        if ($result = $mysqli->store_result()) {
            $result->free();
        }

    } while ($mysqli->more_results() && $mysqli->next_result());
}
$mysqli->close();

You could also add some code that actually checks the results of each of the multiple queries if you were so inclined.

Upvotes: 4

Related Questions