user6358161 user6358161 - 4 months ago 12
MySQL Question

using STORED PROCEDURES: different results in mysqli->query("CALL select_procedure") VS mysqli->query("SELECT ...")

I am migrating all my mysqli queries to STORED PROCEDURE.
It should be as easy as changing one line in the mysqli call, howver, the two following codes give different results:

Regular query, which works correctly:

$query = $this->mysqli->query("SELECT DISTINCT ID FROM user
WHERE
MATCH (name) AGAINST ('* *$sanitized* *') ");

if ($query) {
$nrows = $query -> num_rows;
if ($nrows > 0) {
$searchResult = 'We found '. $nrows .' results';
}
}


CALL to PREPARED STATEMENT, which returns a "fetch_array() on boolean" error:

$query = $this->mysqli->query("CALL myfunction('.$sanitized.')");


where the procedures is defined as:

DELIMITER $$
CREATE PROCEDURE myfunction (sanitized VARCHAR(124))
BEGIN
SELECT DISTINCT ID FROM user
WHERE
MATCH (name) AGAINST ('* *sanitized* *');
END
$$
DELIMITER ;


I can't find a solution and it seems that no one has a similar issue in this forum.

Answer

consider Prepared Statements used with concat() as they often are.

DROP PROCEDURE if exists myStoredProc101;
DELIMITER $$
CREATE PROCEDURE myStoredProc101
(   pSanitized VARCHAR(124)
)
BEGIN
    set @mySql:=concat("SELECT DISTINCT ID FROM user where match(name) against ('* *",pSanitized,"* *')");
    PREPARE stmt1 FROM @mySql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END 
$$
DELIMITER ;

Your stored proc had no chance of working as it wasn't even using your parameter. What you did was bury something inside of a string literal. Also, varchar(124) is a bit odd :p

About the only success people have with prepared statements is with using a User Variable (with an @) versus failed attempts of using Local Variables (from DECLARE). So, that may save you a few hours of head banging in the future.

From the PHP Manual Page Stored Procedures:

Handling result sets

Stored procedures can return result sets. Result sets returned from a stored procedure cannot be fetched correctly using mysqli_query. The mysqli_query function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query to fail returning the user expected result sets.

Result sets returned from a stored procedure are fetched using mysqli_real_query or mysqli_multi_query. Both functions allow fetching any number of result sets returned by a statement, such as CALL. Failing to fetch all result sets returned by a stored procedure causes an error.

Comments