0

I have a stored proc that does a geospatial query. The proc issues two sql statements but only the 2nd one does a query but unfortunately both statements produce a result set. I need the second result set which contains the results of the actual query.

The first statement sets a bounding box: 

SET @bbox = 'POLYGON(($polygon))'; \n
SELECT * , AsText( location ) 
  FROM users
  WHERE Intersects( location, GeomFromText( @bbox ) ) [snipped for brevity]

If I run the above in phpMyAdmin, it works but I get the following message AFTER the SET command is issued and I want to throw this away:

# MySQL returned an empty result set (i.e. zero rows).

On the php side, I build the query string, calling the stored proc and on return the first thing I do is throw away the empty result set.

$query = "CALL usp_queryByPolygon('$polygon', $msg_id, $user_type)";
$result = mysqli_query($cxn, $query) or die("GEOCODE: queryPolygon - " .sql_error());
sql_free_result($result);

After throwing away the result set I now need the results of the query and this is what I have done:

$result = sql_next_result();

The problem is when I try to use this second result set as in:

if(mysqli_num_rows($result) > 0)

I get errors:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given
in /blah/blah/module.php on line 96

To complicate things, all of the above is in a loop and there could be dozens or 100's of polygons to search.

So the question is this: what is the proper way to get that 2nd result set?

mleko
  • 11,650
  • 6
  • 50
  • 71
ppetree
  • 826
  • 3
  • 15
  • 31
  • just as an fyi, functions shown as sql_ as in sql_next_result() are functions in a db abstraction layer and those functions map directly to mysqli functions. – ppetree Nov 16 '11 at 13:14
  • http://stackoverflow.com/questions/1683794/retrieving-multiple-result-sets-with-stored-procedure-in-php-mysqli – wutz Nov 16 '11 at 13:14

2 Answers2

0

You'd better be accurate of what functions you execute. sql_next_result() is no standard PHP function, nor is it in MySQLi which you seem to use. If it's some kind of database class, please just show the methods that class uses. Nobody here can but quess what sql_next_result() does.

Assuming you're talking about mysqli_next_result(), that indeed returns a boolean, you need to call mysqli_use_result() after that in order to retreive the next result set.

CodeCaster
  • 147,647
  • 23
  • 218
  • 272
0

Found out the two statements: SET @bbox and SELECT can be executed sequentially so mysqli and the two results are just fodder that don't need to be dealt with.

ppetree
  • 826
  • 3
  • 15
  • 31