Getting results from a MySQL Stored Procedure in PHP5 using prepared statements

Question

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.

I have a table where I store information about photos, like its id, title, latitude and longitude of the place it was shot etc.

I created the following stored procedure and implement the Haversin Formula based on what I've found here: http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

The procedure is:

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 ;

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.

I have the following test method:

public function CallProcedure( $procedure, $arguments )
    {
        print_r($procedure);
        echo "<br>*************************<br>";

        if( ! $this->_sqlStatement = $this->_dbSession->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 => $value)
                {
                    $bindParamRefs[$key] = &$arguments[$key];
                }
                //put a reference to the bindString inside the bindParamsRefs array
                array_unshift($bindParamRefs, $bindString);

                print_r($bindParamRefs);
                echo "<br>*************************<br>";
                //Bind the params to the query
                if(!call_user_func_array(array($this->_sqlStatement, "bind_param"), $bindParamRefs))
                {
                    return FALSE;
                }

                else
                {
                    print_r($this->_sqlStatement);
                    echo "<br>*************************<br>";
                    if( ! $this->_sqlStatement->execute() )
                    {
                        return FALSE;
                    }

                    else
                    {
                        do
                    {
                        $this->_sqlStatement->store_result();
                        print_r($this->_sqlStatement);
                        echo "<br>*************************<br>";


                        $resultSet = array();
                        $resultMetadata = $this->_sqlStatement->result_metadata();
                        $resultVariables = array();
                        $resultData = array();

                        while($resultField = $resultMetadata->fetch_field())
                        {
                            $resultVariables[] = &$resultData[$resultField->name];
                        }

                        call_user_func_array(array($this->_sqlStatement, "bind_result"), $resultVariables);

                        $i = 0;
                        while( $this->_sqlStatement->fetch() )
                        {
                            $resultSet[$i] = array();
                            foreach($resultData as $fieldName => $fieldData)
                            {
                                $resultSet[$i][$fieldName] = $fieldData;
                            }
                            $i++;
                        }

                        print_r($resultSet);
                        echo "<br>*************************<br>";
                        print_r($resultMetadata);
                        echo "<br>*************************<br>";
                        print_r($resultVariables);
                        echo "<br>*************************<br>";
                        print_r($resultData);
                        echo "<br>*************************<br>";

                        $this->_sqlStatement->free_result();

                    }
                    while($this->_sqlStatement->more_results() && $this->_sqlStatement->next_result());

                    $this->_sqlStatement->close();
                    }
                }
            }
        }

    }

($this->_dbSession is an instance of mysqli)

And the output produced by the code above is:

CALL imagesNearMe(?, ?, ?)
*************************
Array ( [0] => ddi [1] => 38.246214 [2] => 38.246214 [3] => 150 ) 
*************************
mysqli_stmt Object ( [affected_rows] => -1 [insert_id] => 0 [num_rows] => 0 [param_count] => 3 [field_count] => 0 [errno] => 0 [error] => [sqlstate] => 00000 [id] => 1 ) 
*************************
mysqli_stmt Object ( [affected_rows] => 0 [insert_id] => 0 [num_rows] => 0 [param_count] => 3 [field_count] => 8 [errno] => 0 [error] => [sqlstate] => 00000 [id] => 1 ) 
*************************
Array ( ) 
*************************
mysqli_result Object ( [current_field] => 8 [field_count] => 8 [lengths] => [num_rows] => 0 [type] => 1 ) 
*************************
Array ( [0] => [1] => [2] => [3] => [4] => [5] => [6] => [7] => ) 
*************************
Array ( [id] => [title] => [description] => [location] => [lat] => [lng] => [user_id] => [distance] => ) 
*************************

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.

Am I missing something? I'm totally lost here.

Thanks in advance for you help.

UPDATE

The following code:

$dbSession = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);

if($dbSession->connect_errno)
{
    die($dbSession->connect_error);
}

else
{
    $query = "CALL imagesNearMe(38.2462140, 21.735098, 50)";

    if(!$dbSession->multi_query($query))
    {
        die($dbSession->error);
    }

    else
    {
        do
        {
            if($result = $dbSession->store_result())
            {
                var_dump($result->fetch_all());
                echo "<br>******************************************************<br>";
                $result->free();
            }
            else
            {
                die($dbSession->error);
            }
        }
        while($dbSession->more_results() && $dbSession->next_result());

        $dbSession->close();
    }
}

returns all the results I expect but why isn't the prepared statement working?

Geek Num 88 · Accepted Answer · 2012-10-07 08:28:55Z

1

Found your problem

because of the way that call_user_func_array works you need to use references (the "&") and you are looping through the fetch() method on the same references - BUT there is a 2nd result set of "OK" that comes back saying the CALL statement was successful which is overwriting the result set you are looking for with a blank result set

try changing

$i = 0;
while( $this->_sqlStatement->fetch() )
{
     $resultSet[$i] = array();
     foreach($resultData as $fieldName => $fieldData)
     {
          $resultSet[$i][$fieldName] = $fieldData;
     }
     $i++;
}

to

if(!$this->_sqlStatement->fetch())
{
     //break do-while() loop
     break;
}
$resultSet[$i] = array();
foreach($resultData as $fieldName => $fieldData)
{
     $resultSet[$i][$fieldName] = $fieldData;
}
$i++;

you also need to set the initial $i value before the do-while() starts

answered Oct 7, 2012 at 8:28

Geek Num 88

5,3102 gold badges24 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

feugatos Over a year ago

Thanks. I'll try it and report back as soon as I can.

Collectives™ on Stack Overflow

Getting results from a MySQL Stored Procedure in PHP5 using prepared statements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related