MySQLi prepared statement - Commands are out of sync, still returning results
I either lost the plot or someone is playing with my mind: /
I've separated this code from the rest of my app to try and debug, and hardcoded $ guideid for readability.
I have the following code (there is nothing in this script as a test, that is, no other requests) :
<?php
define("DB_HOST", "127.0.0.1");
define("DB_NAME", "xxxxx");
define("DB_USER", "xxxxx");
define("DB_PASS", "xxxxx");
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$stmt = $mysqli->prepare("SELECT `guides_listings`.`listing_id`, `guide_slug`, `guide_name_en`, listing_name, `listing_slug`, `slogo`.`filename` AS `slogoname`, `hlogo`.`filename` AS `hlogoname`, `vlogo`.`filename` AS `vlogoname`
FROM `guides_listings`
JOIN `guides` ON `guides_listings`.`listing_guide` = `guides`.`guide_id`
LEFT JOIN `guides_listings_pics` AS `slogo`
ON `slogo`.`listing_id` = `guides_listings`.`listing_id`
AND `slogo`.`type` = 'slogo'
LEFT JOIN `guides_listings_pics` AS `hlogo`
ON `hlogo`.`listing_id` = `guides_listings`.`listing_id`
AND `hlogo`.`type` = 'hlogo'
LEFT JOIN `guides_listings_pics` AS `vlogo`
ON `vlogo`.`listing_id` = `guides_listings`.`listing_id`
AND `vlogo`.`type` = 'vlogo'
WHERE (`slogo`.`filename` IS NOT NULL OR `hlogo`.`filename` IS NOT NULL OR `vlogo`.`filename` IS NOT NULL)
AND `guides_listings`.`listing_guide` = ?
GROUP BY `guides_listings`.`listing_id`
ORDER BY RAND()
LIMIT 12");
$stmt->bind_param("i",$guideid);
$guideid = 2;
$stmt->execute();
$stmt->bind_result($listing_id,$guide_slug,$guide_name,$listing_name,$listing_slug,$slogo,$hlogo,$vlogo);
while($stmt->fetch()) {
$results->data[] = array('listing_id'=>$listing_id,'guide_slug'=>$guide_slug,'guide_name'=>$guide_name,'listing_name'=>$listing_name,'listing_slug'=>$listing_slug,'slogo'=>$slogo,'hlogo'=>$hlogo,'vlogo'=>$vlogo);
}
$stmt->close();
?>
This results in Commands out of sync; you can't run this command now
after execution ()
However, correct results are returned anyway.
I am missing something obvious as to why it is still working but causing this error?
EDIT
I've just done some testing and this seems to happen in all prepared statements, see example below:
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$stmt = $mysqli->stmt_init();
$stmt->prepare("SELECT `listing_id` FROM `guides_listings` LIMIT 1");
$stmt->execute();
print_r($mysqli);
$stmt->bind_result($listing_id);
$stmt->fetch();
$results->data[] = array('listing_id'=>$listing_id);
$stmt->close();
As a result, print_r is:
mysqli Object
(
[affected_rows] => -1
[client_info] => 5.6.21
[client_version] => 50621
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[error_list] => Array
(
)
[field_count] => 1
[host_info] => 127.0.0.1 via TCP/IP
[info] =>
[insert_id] => 0
[server_info] => 5.6.21
[server_version] => 50621
[stat] => Commands out of sync; you can't run this command now
[sqlstate] => HY000
[protocol_version] => 10
[thread_id] => 19462371
[warning_count] => 0
)
Maybe I am really looking at the error here?
+3
source to share
2 answers
Without a prepared statement:
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($stmt= $mysqli->query("SELECT `listing_id` FROM `guides_listings` LIMIT 1")) {
$stmt->bind_result($listing_id);
$stmt->fetch();
echo "listing_id = ". $listing_id;
$stmt->close();
}else{
printf("Error: %s\n", $mysqli->error);
}
$mysqli->close();
With Prepared Statement:
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT `listing_id` FROM `guides_listings` LIMIT 1";
if ($stmt = $mysqli->prepare($query)) {
$stmt->execute();
/* store result */
$stmt->store_result();
if($stmt->num_rows > 0){
$stmt->bind_result($listing_id);
if ($stmt->fetch()) {
echo "listing_id = ". $listing_id;
}
}
/* free result */
$stmt->free_result();
$stmt->close();
}else{
/*failed to prepare*/
printf("Error: %s\n", $mysqli->error);
}
$mysqli->close();
With a prepared statement (full code):
<?php
define("DB_HOST", "127.0.0.1");
define("DB_NAME", "xxxxx");
define("DB_USER", "xxxxx");
define("DB_PASS", "xxxxx");
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($mysqli->connect_errno) {
printf("Connect failed: %s\n", $mysqli->connect_error);
exit();
}
$query = "
SELECT `guides_listings`.`listing_id`,
`guide_slug`,
`guide_name_en`,
listing_name,
`listing_slug`,
`slogo`.`filename` AS `slogoname`,
`hlogo`.`filename` AS `hlogoname`,
`vlogo`.`filename` AS `vlogoname`
FROM `guides_listings`
JOIN `guides`
ON `guides_listings`.`listing_guide` = `guides`.`guide_id`
LEFT JOIN `guides_listings_pics` AS `slogo`
ON `slogo`.`listing_id` = `guides_listings`.`listing_id`
AND `slogo`.`type` = 'slogo'
LEFT JOIN `guides_listings_pics` AS `hlogo`
ON `hlogo`.`listing_id` = `guides_listings`.`listing_id`
AND `hlogo`.`type` = 'hlogo'
LEFT JOIN `guides_listings_pics` AS `vlogo`
ON `vlogo`.`listing_id` = `guides_listings`.`listing_id`
AND `vlogo`.`type` = 'vlogo'
WHERE ( `slogo`.`filename` IS NOT NULL
OR `hlogo`.`filename` IS NOT NULL
OR `vlogo`.`filename` IS NOT NULL )
AND `guides_listings`.`listing_guide` = ?
GROUP BY `guides_listings`.`listing_id`
ORDER BY Rand()
LIMIT 12
";
$guideid = 2;
if ($stmt = $mysqli->prepare($query)) {
$stmt->bind_param("i", $guideid);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($listing_id,$guide_slug,$guide_name,$listing_name,
$listing_slug,$slogo,$hlogo,$vlogo);
$results= array();
while($stmt->fetch()) {
$results[] = array(
'listing_id'=>$listing_id,'guide_slug'=>$guide_slug,
'guide_name'=>$guide_name,'listing_name'=>$listing_name,
'listing_slug'=>$listing_slug,'slogo'=>$slogo,
'hlogo'=>$hlogo,'vlogo'=>$vlogo
);
}
$stmt->free_result();
$stmt->close();
}else{
$results[] = array("Error:" => $stmt->error);
}
/* close connection */
$mysqli->close();
/* Output results in JSON*/
echo json_encode($results);
+1
source to share