[SOLVED] Stored Procedure

[The Little Guy]

How come this SQL query works in MySQL Administrator, but doesn't work in a web browser, or PHP MyAdmin?

 

call GetNearbyZipCodes('55068',  '10')

 

Error Message:

PROCEDURE murdercup.GetNearbyZipCodes can't return a result set in the given context

[The Little Guy]

Anyone?

[DarkWater]

We'd need to see the procedure.

[The Little Guy]

The Procedure:

CREATE DEFINER=`murdercup`@`%` PROCEDURE `GetNearbyZipCodes`(
    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  B.zip  
FROM  zipCodes  AS  B
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`;
END

 

 

The function:

CREATE DEFINER=`murdercup`@`%` FUNCTION `GetDistance`(lat1  numeric (9,6),
lon1  numeric (9,6),  
lat2  numeric (9,6),
lon2  numeric (9,6)  ) RETURNS decimal(10,5)
BEGIN  
  DECLARE  x  decimal (20,10);
  DECLARE  pi  decimal (21,20);  
  SET  pi = 3.14159265358979323846;  
  SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180  ) + cos(  
lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos( (lon2 * pi/180) -
(lon1 *pi/180)  );
  SET  x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x );
  RETURN  ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;
END

[The Little Guy]

Anyone know why?

[fenway]

Did you try using mysqli_multi_query()?

 

I'd read this PHP bug report too.

[The Little Guy]

Hmm... wonder why this flag isn't supported:

 

CLIENT_MULTI_RESULTS

[The Little Guy]

but cool that works.