CODEWITHSUNDEEP
phpmysqlpdo
Sho Gum Lew
Sho Gum Lew

Reputation: 339

normal MYSQL does not work asking for PDO

I have been trying to run my stored procedure using mysql unsuccessful for quite sometime. whenever I use the code below

$link_id = DbConnection::getInstance('mycon')->connectMysql();

$table_count = mysql_query("SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'mycon' AND table_name LIKE 'table_%' ")

while($row = mysql_fetch_array($table_count)){
     $table = $row["TABLE_NAME"];
     $excute = mysql_query("dummy_2('$table')") or die(mysql_error());
     $result = mysql_fetch_assoc($excute);
     var_dump($result);
}

it gives an error saying

Commands out of sync; you can't run this command now

so when I searched the internet, it said that I need to use MYSQL PDO.. Therefore can anyone convert my above statement to mysql pdo.. since i got no clue about PDO whatsoever

Upvotes: 0

Views: 52

Answers (1)

VolkerK
VolkerK

Reputation: 96159

When you query something from the MySQL database the result is presented as a result set. Actually some queries might have multiple result sets associated. But there can be only one active list of results sets per connection. I.e. you, your script somehow has to close all currently active result sets before you can issue another query.
If e.g. your stored function uses multiple SELECTs the function has multiple result sets and you have to iterate/close/drop them all.

http://dev.mysql.com/doc/refman/5.1/en/stored-routines-syntax.html:

MySQL supports a very useful extension that enables the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client. Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets.

The old, deprecated mysql_* functions do not support multiple result sets - you simply can't iterate/drop them.
The mysqli_* extension does: see http://docs.php.net/mysqli.next-result
And so does PDO: see http://docs.php.net/pdostatement.nextrowset.php

Upvotes: 4

Related Questions