title: Getting results from a MySQL Stored Procedure in PHP5 using prepared statements
body: <p>I'm developing a website using PHP5 and MySQL. I use the mysqlnd driver and I have a class that uses prepared statements to execute any queries I want.</p>
  
  <p>I have a table where I store information about photos, like its id, title, latitude and longitude of the place it was shot etc.</p>
  
  <p>I created the following stored procedure and implement the Haversin Formula based on what I've found here: <a href="http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL" rel="nofollow">http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL</a></p>
  
  <p>The procedure is:</p>
  
  <pre><code>DELIMITER $$
  CREATE PROCEDURE imagesNearMe(IN user_lat DOUBLE, IN user_lng DOUBLE, IN distance INT)
  READS SQL DATA
  BEGIN
  
  DECLARE lat1 DOUBLE;
  DECLARE lng1 DOUBLE;
  DECLARE lat2 DOUBLE;
  DECLARE lng2 DOUBLE;
  
  SET lng1 = user_lng - (distance/ABS(COS(RADIANS(user_lat)) * 69));
  SET lng2 = user_lng + (distance/ABS(COS(RADIANS(user_lat)) * 69));
  SET lat1 = user_lat - (distance/69);
  SET lat2 = user_lat + (distance/69);
  
  SELECT id, title, description, location, lat, lng, user_id,
          3956 * 2 * ASIN( SQRT( 
          POWER( SIN( (user_lat - ABS(lat)) * pi() / 180 / 2), 2) + 
          COS(user_lat * pi() / 180) * COS( ABS(lat) * pi() * 180) * POWER( SIN( (user_lng - lng) * pi() / 180 / 2), 2) ) ) as distance
          FROM images
          WHERE (lat IS NOT NULL) AND (lng IS NOT NULL)
              AND (lat BETWEEN lat1 AND lat2)
              AND (lng BETWEEN lng1 AND lng2)
          ORDER BY distance ASC;
  
  END$$
  DELIMITER ;
  </code></pre>
  
  <p>The procedure works fine when I call it from the MySQL console and returns the data expected. The problem is when I try to call the procedure from my PHP code. For some reason I can't get the data returned by the procedure.</p>
  
  <p>I have the following test method:</p>
  
  <pre><code>public function CallProcedure( $procedure, $arguments )
      {
          print_r($procedure);
          echo "&lt;br&gt;*************************&lt;br&gt;";
  
          if( ! $this-&gt;_sqlStatement = $this-&gt;_dbSession-&gt;prepare($procedure))
          {
              return FALSE;
          }
  
          else
          {
              //Prepare succeeded
              if($arguments)
              {
                  //if we have arguments we have to prepare and bind the to the query
                  //get the bind string
                  $bindString = array_shift($arguments);
  
                  //get references to the parameters passed to the query
                  $bindParamRefs = array();
                  foreach($arguments as $key =&gt; $value)
                  {
                      $bindParamRefs[$key] = &amp;$arguments[$key];
                  }
                  //put a reference to the bindString inside the bindParamsRefs array
                  array_unshift($bindParamRefs, $bindString);
  
                  print_r($bindParamRefs);
                  echo "&lt;br&gt;*************************&lt;br&gt;";
                  //Bind the params to the query
                  if(!call_user_func_array(array($this-&gt;_sqlStatement, "bind_param"), $bindParamRefs))
                  {
                      return FALSE;
                  }
  
                  else
                  {
                      print_r($this-&gt;_sqlStatement);
                      echo "&lt;br&gt;*************************&lt;br&gt;";
                      if( ! $this-&gt;_sqlStatement-&gt;execute() )
                      {
                          return FALSE;
                      }
  
                      else
                      {
                          do
                      {
                          $this-&gt;_sqlStatement-&gt;store_result();
                          print_r($this-&gt;_sqlStatement);
                          echo "&lt;br&gt;*************************&lt;br&gt;";
  
  
                          $resultSet = array();
                          $resultMetadata = $this-&gt;_sqlStatement-&gt;result_metadata();
                          $resultVariables = array();
                          $resultData = array();
  
                          while($resultField = $resultMetadata-&gt;fetch_field())
                          {
                              $resultVariables[] = &amp;$resultData[$resultField-&gt;name];
                          }
  
                          call_user_func_array(array($this-&gt;_sqlStatement, "bind_result"), $resultVariables);
  
                          $i = 0;
                          while( $this-&gt;_sqlStatement-&gt;fetch() )
                          {
                              $resultSet[$i] = array();
                              foreach($resultData as $fieldName =&gt; $fieldData)
                              {
                                  $resultSet[$i][$fieldName] = $fieldData;
                              }
                              $i++;
                          }
  
                          print_r($resultSet);
                          echo "&lt;br&gt;*************************&lt;br&gt;";
                          print_r($resultMetadata);
                          echo "&lt;br&gt;*************************&lt;br&gt;";
                          print_r($resultVariables);
                          echo "&lt;br&gt;*************************&lt;br&gt;";
                          print_r($resultData);
                          echo "&lt;br&gt;*************************&lt;br&gt;";
  
                          $this-&gt;_sqlStatement-&gt;free_result();
  
                      }
                      while($this-&gt;_sqlStatement-&gt;more_results() &amp;&amp; $this-&gt;_sqlStatement-&gt;next_result());
  
                      $this-&gt;_sqlStatement-&gt;close();
                      }
                  }
              }
          }
  
      }
  </code></pre>
  
  <p>($this->_dbSession is an instance of mysqli)</p>
  
  <p>And the output produced by the code above is:</p>
  
  <pre><code>CALL imagesNearMe(?, ?, ?)
  *************************
  Array ( [0] =&gt; ddi [1] =&gt; 38.246214 [2] =&gt; 38.246214 [3] =&gt; 150 ) 
  *************************
  mysqli_stmt Object ( [affected_rows] =&gt; -1 [insert_id] =&gt; 0 [num_rows] =&gt; 0 [param_count] =&gt; 3 [field_count] =&gt; 0 [errno] =&gt; 0 [error] =&gt; [sqlstate] =&gt; 00000 [id] =&gt; 1 ) 
  *************************
  mysqli_stmt Object ( [affected_rows] =&gt; 0 [insert_id] =&gt; 0 [num_rows] =&gt; 0 [param_count] =&gt; 3 [field_count] =&gt; 8 [errno] =&gt; 0 [error] =&gt; [sqlstate] =&gt; 00000 [id] =&gt; 1 ) 
  *************************
  Array ( ) 
  *************************
  mysqli_result Object ( [current_field] =&gt; 8 [field_count] =&gt; 8 [lengths] =&gt; [num_rows] =&gt; 0 [type] =&gt; 1 ) 
  *************************
  Array ( [0] =&gt; [1] =&gt; [2] =&gt; [3] =&gt; [4] =&gt; [5] =&gt; [6] =&gt; [7] =&gt; ) 
  *************************
  Array ( [id] =&gt; [title] =&gt; [description] =&gt; [location] =&gt; [lat] =&gt; [lng] =&gt; [user_id] =&gt; [distance] =&gt; ) 
  *************************
  </code></pre>
  
  <p>As you can see I even though the statement executes properly, no data is ever returned, even though by calling the exact same procedure with the exact same arguments from the mysql console returns 4 results.</p>
  
  <p>Am I missing something? I'm totally lost here.</p>
  
  <p>Thanks in advance for you help.</p>
  
  <hr>
  
  <p>UPDATE</p>
  
  <hr>
  
  <p>The following code:</p>
  
  <pre><code>$dbSession = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);
  
  if($dbSession-&gt;connect_errno)
  {
      die($dbSession-&gt;connect_error);
  }
  
  else
  {
      $query = "CALL imagesNearMe(38.2462140, 21.735098, 50)";
  
      if(!$dbSession-&gt;multi_query($query))
      {
          die($dbSession-&gt;error);
      }
  
      else
      {
          do
          {
              if($result = $dbSession-&gt;store_result())
              {
                  var_dump($result-&gt;fetch_all());
                  echo "&lt;br&gt;******************************************************&lt;br&gt;";
                  $result-&gt;free();
              }
              else
              {
                  die($dbSession-&gt;error);
              }
          }
          while($dbSession-&gt;more_results() &amp;&amp; $dbSession-&gt;next_result());
  
          $dbSession-&gt;close();
      }
  }
  </code></pre>
  
  <p>returns all the results I expect but why isn't the prepared statement working? </p>