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);
}
?>