BOTJr. BOTJr. - 3 months ago 7
PHP Question

Query works with mysql but doesn't work with php sql call

I have a products table stored inside the database which contains info related to the product.I have a stored procedure that gets the rows with maximum quantity.

Whenever i try to call that stored procedure from the php it returs false while when i run the same query in mysql console, it returns me the rows.

PHP Code:

$resVal=$mysqli->query('CALL get_max_quant_rows("'.$company.'","'.$type.'","'.$limit.'")');
$countVal=$resVal->fetch_row();
$countVal=$countVal[0];
$results = $mysqli->query('CALL get_max_quant_rows("'.$company.'","'.$type.'","'.$limit.'")');
var_dump('CALL get_max_quant_rows("'.$company.'","'.$type.'","'.$limit.'")');
var_dump($results);


The ouput of the var_dump of the above queries give me:

string(72) "CALL get_max_quant_rows("1471941595186287666657bc0bdb1c25d","Cakes","1")" bool(false)


I have shown you the var_dump of the query as to show you that the values are going perfectly inside the stored procedure.

SQL console

When i ran the same query inside the console,it ran and it gave me the results.What could pe the possible reason for this behaviour?

Stored Procedure:

DELIMITER $$

USE `dboxyz`$$

DROP PROCEDURE IF EXISTS `get_max_quant_rows`$$

CREATE PROCEDURE `get_max_quant_rows`(company VARCHAR(8000),product_type VARCHAR(8000),limiter INT)
BEGIN
DECLARE emptyCheckFirst BIT;
DECLARE emptyCheckSecond BIT;
SET emptyCheckFirst=`dboxyz`.isNullOrEmpty(company);
SET emptyCheckSecond=`dboxyz`.isNullOrEmpty(product_type);
IF (emptyCheckFirst=0 AND emptyCheckSecond=0)
THEN
SELECT p1.id,p1.price,p1.product_code,p1.product_name,p1.quantity,p1.amount,p1.companyId FROM products p1
INNER JOIN (SELECT product_code,MAX(quantity) max_quantity FROM products WHERE companyId=company AND `type`=product_type
GROUP BY product_code) p2 ON p1.product_code=p2.product_code AND p1.quantity=p2.max_quantity LIMIT limiter;
END IF;
IF emptyCheckFirst=1 AND emptyCheckSecond=1
THEN
SELECT p1.id,p1.price,p1.product_code,p1.product_name,p1.quantity,p1.amount,p1.companyId FROM products p1
INNER JOIN (SELECT product_code,MAX(quantity) max_quantity FROM products
GROUP BY product_code) p2 ON p1.product_code=p2.product_code AND p1.quantity=p2.max_quantity LIMIT limiter;
END IF;
END$$

DELIMITER ;


Update:
Error given by the DB


Commands out of sync; you can't run this command now


DELIMITER $$

USE `dboxyz`$$

DROP FUNCTION IF EXISTS `isNullOrEmpty`$$

CREATE FUNCTION `isNullOrEmpty`(xx VARCHAR(8000)) RETURNS BIT(1)
BEGIN
DECLARE somevariable VARCHAR(8000);
SET somevariable=xx;
IF (somevariable IS NOT NULL AND LEN(somevariable)>0)
THEN
RETURN 0;
ELSE
RETURN 1;
END IF;
END$$

DELIMITER ;

Answer

MySQL stored procedures can return more than one result set, that is the reason to clear all results before do another query using the same connection.

Just use next_result, and do another query:

$results = $mysqli->query("CALL stored_procedure()");    

$mysqli->next_result();

$results2 = $mysqli->query("CALL another_stored_procedure()");