Edit report at http://bugs.php.net/bug.php?id=54444&edit=1
ID: 54444 Updated by: u...@php.net Reported by: peter dot colclough at toolstation dot com Summary: Multiple Queries on a single conenction Status: Bogus Type: Bug Package: MySQLi related Operating System: Ubuntu 10 64 bit PHP Version: 5.3SVN-2011-04-01 (SVN) Block user comment: N Private report: N New Comment: Hmm, you can prepare as many statements as you want per connection. But once you have executed a statement on a connection you must fetch its results before you can execute another statement. The result set "blocks" the line. You can, of course, do an implicit fetch on the C level upon execute but that's the exact opposite of the default fetch method (unbuffered) used for prepared statements by MySQL. Its a one-liner to do that fetch in user land. No need for changes on the C level. $mysqli = new mysqli("localhost", "root", "root", "test"); $stmt1 = $mysqli->prepare("SELECT 1 AS _one FROM DUAL"); $stmt2 = $mysqli->prepare("SELECT 2 AS _two FROM DUAL"); /* execute */ if (!$stmt1->execute()) printf("[001] [%d] %s\n", $stmt1->errno, $stmt1->error); /* clear line by fetching result set */ $res1 = $stmt1->get_result(); /* execute */ if (!$stmt2->execute()) printf("[002] [%d] %s\n", $stmt2->errno, $stmt2->error); /* clear line by fetching result set */ $res2 = $stmt2->get_result(); /* fetching second first */ while ($row = $res2->fetch_assoc()) var_dump($row); $res2->free(); while ($row = $res1->fetch_assoc()) var_dump($row); $res1->free(); Previous Comments: ------------------------------------------------------------------------ [2011-05-09 16:35:37] peter dot colclough at toolstation dot com Thanks for teh feedback. I was also getting that error, just wanted to make sure it wasn't 'me'... but actually expected behaviour. Am now devbeloping my own, that allows multiple statements per connection, as well as multiple 'prepare' statements. This will be open sourced when ready. The current mysqli interface should have been able to do this, but it was obviously decided not to allow it... which is a bit of a pain. Thanks again for your input ------------------------------------------------------------------------ [2011-05-09 16:14:45] u...@php.net You can answer this question yourself by adding a bit of error handling to your script such as ... $mysqli = new mysqli("localhost", "root", "root", "test"); $stmt1 = $mysqli->prepare("SELECT 1 AS _one FROM DUAL"); $stmt2 = $mysqli->prepare("SELECT 2 AS _two FROM DUAL"); if (!$stmt1->execute() || !($meta1 = $stmt2->result_metadata())) printf("[001] [%d] %s\n", $stmt1->errno, $stmt1->error); if (!$stmt2->execute() || !($meta2 = $stmt2->result_metadata())) printf("[002] [%d] %s\n", $stmt2->errno, $stmt2->error); ... and the answer is: [002] [2014] Commands out of sync; you can't run this command now ------------------------------------------------------------------------ [2011-04-01 16:13:48] peter dot colclough at toolstation dot com Description: ------------ Hi, trying to build a generic DB object handler for mySqli, and have hit an issue where we can't have more than one open query on the same connection. Is this a bug or 'expected behaviour'? Looking at the mysqli.c source code, it looks like it should have been possible, but it looks like the second object overwrites the first... I have put a sample snippet below of what I am trying to achieve.... if this helps. Any help greatly appreciated.... OS: 2.6.32-21-generic #32-Ubuntu SMP Fri Apr 16 08:09:38 UTC 2010 x86_64 GNU/Linux PHP Version => 5.3.2-1ubuntu4.5 Test script: --------------- -------------- Code Snippet ----------------------------- $sqlstock = 'select foo1 from bar1 where foo1 =?'; $sqltime = 'select foo2, foo3 from bar2 where foo4 =?'; $varinp = "XXXXXXXXXXXX"; $abindVars = array(0=>$varinp); $varProd = ''; $conn = dbi->db_conn; $sprod = ''; $timestart = microtime_float(); // Get a statement $aRes = array(); $aRes2 = array(); // Init 2 Statements $stmt = mysqli_stmt_init($conn); $stmt2 = mysqli_stmt_init($conn); // Prepare 2 statements mysqli_stmt_prepare($stmt,$sqlstock); mysqli_stmt_prepare($stmt2,$sqltime); // Set the bind variable $varinp = "PXX00019263"; // Bind the statements mysqli_stmt_bind_param($stmt,'s', &$varinp); mysqli_stmt_bind_param($stmt2,'s', &$varinp); // Execute - Second one fails mysqli_stmt_execute($stmt); mysqli_stmt_execute($stmt2); // Set up field Defs $aFieldDefs = array(); $aFieldDefs2 = array(); // Get result Metadata $result = mysqli_stmt_result_metadata($stmt); $result2 = mysqli_stmt_result_metadata($stmt2); $nCount = 0; while($aFieldDefs[$nCount] = mysqli_fetch_field($result)){ echo('Field = '.print_r($aFieldDefs, true)."\r\n"); $aRes[$aFieldDefs[$nCount++]->name] = null; } $nCount = 0; while($aFieldDefs2[$nCount] = mysqli_fetch_field($result2)){ echo('Field = '.print_r($aFieldDefs2, true)."\r\n"); $aRes2[$aFieldDefs2[$nCount++]->name] = null; } // Bind Results mysqli_stmt_bind_result($stmt, &$aRes['foo1']); mysqli_stmt_bind_result($stmt2, &$aRes2['foo2'], &$aRes2['foo3']) // Fetch Results mysqli_stmt_fetch($stmt); mysqli_stmt_fetch($stmt2); echo(print_r($aRes, true)."\r\n"); echo(print_r($aRes2, true)."\r\n"); --------------- End Code Snippet ---------------------- Expected result: ---------------- Array ( [foo1] => 'PXX00019263' ) Array ( [foo2] => 2009-09-15 12:05:02 [foo3] => 0000-00-00 00:00:00 ) Actual result: -------------- Array ( [foo1] => ) Array ( [foo2] => 2009-09-15 12:05:02 [foo3] => 0000-00-00 00:00:00 ) ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/bug.php?id=54444&edit=1