Asked  10 Months ago    Answers:  5   Viewed   7 times

The Documentation of the Error Mentioned in the Title Says

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

From here: http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html

But In First Query I am not fetching any data from mysql database, I am just inserting. And In second Query I am getting the data from database.

Here is My code

$connection = mysqli_connect("localhost","username","password","tbl_msgs");
if(mysqli_connect_errno($connection))
{
    die("Failed to connect to MySQL: " . mysqli_connect_error());
}
$query = "INSERT INTO users (total_comments, total_views) 
          VALUES ({$total_comments}, {$total_views});";

$query .= "INSERT INTO msgs (notifications) VALUES ({$notifications})";

mysqli_multi_query($connection,$query);

Upto this Step every thing is fine. But When I execute the following query It gives the Error

$select_query = "SELECT * FROM msgs WHERE msg_id = {$msg_id}";

$result_set = mysqli_query($connection,$select_query);

if(!$result_set) {
    die(mysqli_error($connection)); 
}

Here it gives the Error Commands out of sync; you can't run this command now. I can't understand this situation

Note: There is any Problem in the Query, I have executed the same query directly to PHPMyAdmin and it works fine.

 Answers

2

There are result set pending from the query:

mysqli_multi_query($connection,$query);

You need to use/store result before you can proceed with next query after: Since you look like you don't really care about the first result set, do this after the multi query..

do
{
    $result = mysqli_store_result($connection);
    mysqli_free_result($result);
}while(mysqli_next_result());

Another alternative is to close the connection and starts it again..

mysqli_close($connection);
$connection = mysqli_connect("localhost","username","password","tbl_msgs");

It all depends on your requirements.

Tuesday, August 17, 2021
 
sohum
 
4

Got the Answer! It seems like codeigniter's mysql driver has bugs handling stored procedures.

I changed the drivers from mysql to mysqli in the config/database file by changing

$db['default']['dbdriver'] = 'mysql';

to

$db['default']['dbdriver'] = 'mysqli';

Post that i modified the system/database/drivers/mysqli/mysqli_result.php file and added the below function

function next_result()
{
  if (is_object($this->conn_id))
  {
      return mysqli_next_result($this->conn_id);
  }
}

and modified the model as below

$db = $this->load->database('mailbox',TRUE);
$qry_res = $db->query('Call circle_pending_p()');

echo $db->_error_message();
$res = $qry_res->result_array();

$qry_res->next_result();
$qry_res->free_result();

if (count($res) > 0) {
      return $res;
} else {
      return 0;
}

This solved the problem!

Thursday, April 1, 2021
 
2

Try it with

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

sscce:

<?php
ini_set('display_errors', 'on');
error_reporting(E_ALL|E_STRICT);

$mysqli = new mysqli("localhost", "localonly", "localonly", "test");
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %sn", mysqli_connect_error());
    exit();
}

$mysqli->query('CREATE TEMPORARY TABLE City (ID int auto_increment, `Name` varchar(32), primary key(ID))') or die($mysqli->error);

$stmt = $mysqli->prepare("INSERT INTO City (`Name`) VALUES (?)") or die($mysqli->error);
$stmt->bind_param('s', $city) or die($stmt->error);
foreach(range('A','Z') as $c) {
    $city = 'city'.$c;
    $stmt->execute() or die($stmt->error);
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if (!$mysqli->multi_query($query)) {
    trigger_error('multi_query failed: '.$mysqli->error, E_USER_ERROR);
}
else {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("'%s'n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------n");
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
}

prints

'localonly@localhost'
-----------------
'cityU'
'cityV'
'cityW'
'cityX'
'cityY'

without warnings/notices.

Thursday, April 1, 2021
 
saad
 
4

You do realize the INSERT INTO query can insert multiple rows in a single query?

$parts  = array();

foreach($qar['data'] as $q){

    $uid    = $q['uid'];
    $type   = $q['type'];
    $chldst = $q['chldst'];
    $time   = $q['date'];
    $msg    = $q['msg'];

    $parts[] = "('$uid', '$type', '$chldst', '$time', '$msg')";
 }

$query  = "INSERT INTO `messages` (`uid`, `type`, `chldst`, `time`, `message`)";
$query .= "VALUES ".implode(', ', $parts);

mysqli_query($db, $query)
Saturday, May 29, 2021
 
Puneet
 
3

You have to consume all selects, and navigate to next result

$sql="";
if (mysqli_multi_query($link, $sql)) {
    do {
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_array($result)) {
                array_push($arrows,$row);
            }
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($link));
}
Wednesday, August 4, 2021
 
rkeet
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share