MySQLi prepared statement - Commands out of sync still returning results

Question

I've either lost the plot or someone is playing with my mind :/

I have separated this code from the rest of my application to try and debug, also hardcoded $guideid for ease of reading.

I have the following code (There is nothing else in this script as a test i.e. no other queries):

     <?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 execute()

However the correct results are still returned.

Am I missing something obvious as to why it still works yet issues this error?

EDIT

I've just carried out some testing and this seems to be happening on all prepared queries, 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();

The print_r results in this:

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'm actually looking at a bug here?

@GolezTrol Please read the question in the full before answering. There are no other queries in this script as per the question ("I have the following code (There is nothing else in this script as a test):") — traxwriter, Dec 26 '14 at 13:57
This can be a great help for a solution: http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html — Orel Eraki, Dec 27 '14 at 00:33
@OrelEraki as far as I'm aware this is only relevant where there are other queries, however this is a standalone script I will edit my question to show this. — traxwriter, Dec 27 '14 at 00:53

score 1 · Answer 1 · answered Dec 27 '14 at 00:40

1

Assign $guideid before the bind.
Try calling $stmt->store_result() between execute() and bind_result().
It doesn't look like $stmt is actually defined. Typically I do $stmt = mysqli->prepare(...)

answered Dec 27 '14 at 00:40

wmassingham

381
1
5
21

I've moved $guide id, but no change and store_result the same. I've edited my question to show $stmt = $mysqli – traxwriter Dec 27 '14 at 00:52

meda · Answer 2 · 2014-12-27T15:00:07.127

Without 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 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);

I don't have mysqlnd on this particular setup so could not use get_result or fetch_assoc with a statement and to be honest quite like bind_result and fetch now (well most of the time) — traxwriter, Dec 27 '14 at 01:14
@traxwriter I see what you mean then see my edit, I adapt to bind_result — meda, Dec 27 '14 at 01:19
No change, see my question edit, I may be looking for something that isn't there. — traxwriter, Dec 27 '14 at 01:32
@traxwriter your code looks correct beside `$result->data[]` since you insisted that this is the whole script then it will be an undefined object. Im not sure what else to suggest — meda, Dec 27 '14 at 02:33
Yes, but this is not a prepared statement, there are no issues unless it's prepared. — traxwriter, Dec 27 '14 at 10:20
@traxwriter I elaborated my answer with few version, if you still get this error, you need to store results and free them. Let me know exactly what issue you get, Also make sure you are using my snippet exactly becuase I cannot reproduce your error, We will sort this out — meda, Dec 27 '14 at 15:02

MySQLi prepared statement - Commands out of sync still returning results

2 Answers2

Without Prepared Statement:

With Prepared statement:

With Prepared Statement (Full Code):