BOTJr. BOTJr. - 1 year ago 57
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 Source

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()");