mysql 命令不同步_MySQL存储过程导致“命令不同步”

Call procedure works all right in MySQL terminal, but in PHP, caused Commands out of sync; you can't run this command nowCommands out of sync; you can't run this command now

My procedure is

delimiter $$

create procedure getMostSimilar (IN vU_ID INT, IN voffset INT, IN vsize INT)

BEGIN

set @offset = voffset;

set @size = vsize;

set @uid = vU_ID;

prepare SimilarStmt from

"SELECT U_ID, getSimilarity(U_ID, ?) AS similar FROM Answer WHERE U_ID != ? GROUP BY U_ID ORDER BY similar DESC LIMIT ?, ?";

execute SimilarStmt using @uid, @uid, @offset, @size;

deallocate prepare SimilarStmt;

END

$$

where getSimilarity is a function.

In PHP:

function getMostSimilar($U_ID, $offset, $size){

$query = sprintf("CALL getMostSimilar(%s, %s, %s)",

$U_ID, $offset, $size);

$result = mysql_query($query);

print mysql_error();

if (!$result){

return $query;

}

$ans = array();

$len = 0;

while($row = mysql_fetch_assoc($result)){

$ans[$len] = $row;

$len++;

}

return $ans;

}

What should I do now? Thanks!

解决方案C.5.2.14. Commands out of sync 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.

EDIT

I think you need to rewrite the getMostSimilar stored procedure, instead of using prepare and execute (which I thinks is fooling mysql) if you use the parameters in the procedure like in this example I think your error will be fixed.

HTH