Stored Procedure returning only 'false' statments

[Thrule]

I have included the code below.  The idea is that by calling "select_sale_items(false)" you get only 2 of both types of product, to display on the home page under a quick Sales teaser category.  But by calling "select_sale_items(true)" you get all of the sales, to be displayed on an actual sales page.  The problem is the FALSE section works fine, but I can't get the TRUE section to work.  I.E. I call "select_sale_items(false)" and it works great.  I call "select_sale_items(true)" and I get the error "mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given".  I am guessing that it is executing fine when I call FALSE simply because it is automatically going to the ELSE statement in the stored procedure.  I could be wrong.  Any help would be greatly appreciated!  If anyone needs any additional info to figure this out just let me know.

 

DELIMITER $$
CREATE PROCEDURE select_sale_items (get_all BOOLEAN)
BEGIN

IF get_all = 1 THEN 

SELECT CONCAT("S", sp.id) AS sku, sa.price AS sale_price, sc.category, sp.image, sp.name, sp.price, sp.stock, sp.description FROM sales AS sa INNER JOIN 

soap_products AS sp ON sa.product_id=sp.id INNER JOIN soap_categories AS sc ON sc.id=sp.soap_category_id WHERE sa.product_type="soap" AND ((NOW() BETWEEN 

sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) )

UNION 

SELECT CONCAT("O", sc.id), sa.price, gp.category, gp.image, s.size, spt.price, spt.stock, gp.description FROM sales AS sa INNER JOIN specific_products AS spt 

ON sa.product_id=spt.id INNER JOIN sizes AS s ON s.id=spt.size_id INNER JOIN general_products AS gp ON gp.id=spt.general_products_id WHERE 

sa.product_type="other" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) );

ELSE 

(SELECT CONCAT("S", sp.id) AS sku, sa.price AS sale_price, sc.category, sp.image, sp.name FROM sales AS sa INNER JOIN soap_products AS sp ON 

sa.product_id=sp.id INNER JOIN soap_categories AS sc ON sc.id=sp.soap_category_id WHERE sa.product_type="soap" AND ((NOW() BETWEEN sa.start_date AND 

sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ) ORDER BY RAND() LIMIT 2) 

UNION 

(SELECT CONCAT("O", spt.id), sa.price, gp.category, gp.image, s.size FROM sales AS sa INNER JOIN specific_products AS spt ON sa.product_id=spt.id INNER JOIN 

sizes AS s ON s.id=spt.size_id INNER JOIN general_products AS gp ON gp.id=spt.general_products_id WHERE sa.product_type="other" AND ((NOW() BETWEEN 

sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) ) ORDER BY RAND() LIMIT 2);

END IF;
END$$
DELIMITER ;

 

MySQL Version 5.1.56

PHP Version 5.2.17

 

Here is the script I am using simply to test whether it is working or not.

 

<?php
require($_SERVER['DOCUMENT_ROOT'] . '/includes/config.inc.php');
require(MYSQL);
$r = mysqli_query($dbc, "CALL select_sale_items(true)");
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
    print_r($row);
}

?>

[kickstart]

Hi

 

I can't see anything obvious, but I expect the issue is with the actual select failing.

 

Having you tried copying the SELECT statements out and trying them each individually directly on the database?

 

All the best

 

Keith