0

I'm trying to convert a 0-100 score to a #1, #2, #3, ... #n benchmark rank. It order to do this, I've planned the following query:

$q= "SELECT @rownum:=0;";
$q.=" INSERT INTO ranks (`uid`, `rank`, `sample_date`) (SELECT user_id, @rownum:=@rownum+1, NOW() FROM `scores` WHERE 1 ORDER BY score DESC)";

This runs fine in the SQL console (phpmyadmin) but when trying to run through PHP's MySQLi, using their multi_query I'm running the following error:

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

My multi_query wrapper: (in a class that extends mysqli)

public function multiQuery($query) {
    if (@parent::multi_query($query)) {
        $i = 0; 
        do { 
            $i++; 
        } while (@parent::next_result()); 
    }
    if (!$result) {
        printf("MySQLi error:<br><b>%s</b><br>%s <br>", $this->error, $query);
    }

    return $result;
}

Why am I getting that error?

Noam
  • 3,341
  • 4
  • 35
  • 64
  • Take a look at http://dev.mysql.com/doc/refman/5.1/en/commands-out-of-sync.html – pradeepcep Dec 04 '14 at 15:47
  • @CEP Thanks but not very informative (didn't help me understand) – Noam Dec 04 '14 at 15:48
  • I am no expert, but it appears you have to _use_ the result with `use_result()` before you can move on to the next result with `next_result()`. This example should be helpful: http://php.net/manual/en/mysqli.use-result.php#refsect1-mysqli.use-result-examples – pradeepcep Dec 04 '14 at 15:53
  • 1
    What do you want to accomplish with the `do-while` loop? You are checking `if (!$result)`, but you haven't assigned anything to it previously. – pradeepcep Dec 04 '14 at 16:27

1 Answers1

1

You must use mysqli_store_result() or mysqli::use_result() before you can retrieve the results of the query, or move on to the next result. I believe you are looking for something like this:

public function multiQuery($query)
{
    if (@parent::multi_query($query))
    {
        do
        {
            if ($result = @parent::use_result())
            {
                return $result;
                // this will end the function on the first result encountered.
                // If that is not what you desire, you could create an array of results,
                // and return it after the do-while loop finishes.
            }
            else
            {
                printf("MySQLi error:<br><b>%s</b><br>%s <br>", $this->error, $query);
                return NULL;  // or you could return the error encountered
            }
        } while (@parent::next_result()); 
    }
}

Hope that helps :)

pradeepcep
  • 902
  • 2
  • 8
  • 24
  • Not sure I totally understand why I need to do `use_results` when I'm performing a `INSERT` (not `SELECT`) – Noam Dec 04 '14 at 17:00
  • I have been reading up on that, and as explained here (http://stackoverflow.com/a/4875174/3186769), _"when you use `multi_query()`, you are firing off queries. But you need to handle the result of those queries before you move on"_. What this means is that you _have_ to take care of the `result` of the queries, by either storing them and using them (`use_result()`) or by freeing the memory associated with the result (`free_result()` - http://php.net/manual/en/mysqli-result.free.php). Hope that helped `:)`. – pradeepcep Dec 04 '14 at 17:39
  • **Update:** It is because mysqli uses unbuffered queries by default. So, using `store_result()` or `use_result()` essentially buffers them in the mysqli client. Sources: http://stackoverflow.com/a/3632320/3186769 and http://stackoverflow.com/a/614741/3186769. This page is also worth a look: http://php.net/manual/en/mysqlinfo.concepts.buffering.php – pradeepcep Dec 04 '14 at 18:05