Commands out of sync

[The Little Guy]

When I run these two commands:

$sql = $db->query("CALL GetBusinesses('$bName', $zip, $radius)");
echo $numSearchResults = $db->getOne("select FOUND_ROWS()");

 

Mysql returns this:

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

 

Why is it doing this? How can I approach a fix to this?

 

Here is my Routine:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`ryan`@`%` PROCEDURE `GetBusinesses`(
    business varchar (50),
    zipbase  varchar (6), 
    `range`  numeric (15)
)
BEGIN 
DECLARE  lat1  decimal (5,2); 
DECLARE  long1  decimal (5,2); 
DECLARE  rangeFactor  decimal (7,6); 
SET  rangeFactor = 0.014457; 
SELECT  `lat`,`log`  into  lat1,long1  FROM  zipcodes  WHERE  zip = zipbase;
SELECT SQL_CALC_FOUND_ROWS B.zip, B.city, B.state, GetDistance(lat1,long1,B.`lat`,B.`log`) as dist, 
C.id, C.name, C.phone, C.address,
MATCH (C.name, C.searchName) AGAINST(business IN BOOLEAN MODE) as score
FROM  zipcodes B JOIN businesses C ON(C.zip = B.zip)
WHERE   
    B.`lat`  BETWEEN  lat1-(`range`*rangeFactor)  AND  lat1+(`range`*rangeFactor)
    AND B.`log`  BETWEEN  long1-(`range`*rangeFactor)  AND  long1+(`range`*rangeFactor)
    AND GetDistance(lat1,long1,B.`lat`,B.`log`) <= `range` 
    AND MATCH (C.name, C.searchName) AGAINST(business IN BOOLEAN MODE)
    ORDER BY score desc, dist;
END

[DavidAM]

When you execute a Stored Procedure in mySql. It returns an "extra" resultset. If your procedure does a single SELECT to return a single resultset, mySql returns two. If your procedure just does an UPDATE and returns NO resultsets, mySql returns one. This extra resultset indicates the success (or failure) of the procedure. Basically, a return code.

 

The standard mysql client (mysql_query(), etc) can NOT handle this extra resultset at all. Basically, if you are using the standard client, you have to close the connection after executing a stored procedure and then open a new connection for any future queries.

 

The "improved" mysql client (mysqli object) CAN handle this extra resultset. You have to look at the multi_query method of the object (as well as more_results() and next_result()).

 

[The Little Guy]

I am using mysqli_multi_query

[DavidAM]

Then you have to process all (both) of the result sets. The logic would look something like this:

[*]mysqli_multi_query($sql) - [Returns a boolean - false on failure]

[*]mysqli_store_result() -or- mysqli_use_result()

[*]mysqli_more_results() [Returns a boolean - true if there are more]

[*]mysqli_next_result()

[*]Repeat from #2 until more_results() returns false

 

If you need more specifics, post the code from $db->query() and $db->getOne().

[The Little Guy]

huh... I guess I am not doing multi_query.... I thought I was....

 

But here is the code from the two:

public function query($query){
$this->sql = mysqli_query($this->db, $query)or die(mysqli_error($this->db));
return $this->sql;
}
public function getOne($query){
$this->sql = $this->query($query);
$row = mysqli_fetch_array($this->sql);
return $row[0];
}

[mikosiko]

take a look here for my answer and adjust your code accordingly:

http://www.phpfreaks.com/forums/index.php?topic=325652.msg1534675#msg1534675