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

Question

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 returns 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.

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 ;

If you get false, then the query failed. Have the DB tell you WHY it failed: if ($result === false) { die(mysqli_error($mysqli)); } — Marc B
– Marc B, Commented Sep 2, 2016 at 15:34
And since you've tagged this with mysql, what's with the dbo business? That's MSSQL, and mysql doesn't have an isnullorempty function. — Marc B
– Marc B, Commented Sep 2, 2016 at 15:35
ok. so your db is just called dbo. that's fine. but that still leaves isnullorempty as a non-existent function (unless that's a UDF or whatever you defined elsewhere and didn't show). And in any case, check the error code. Until you know WHAT the error is, we can only flail around blindly in the dark. — Marc B
– Marc B, Commented Sep 2, 2016 at 15:37

Ivan Cachicatari · Accepted Answer · 2016-09-02 15:48:32Z

1

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

answered Sep 2, 2016 at 15:48

Ivan Cachicatari

4,3143 gold badges24 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related