Invoking Stored Procedures - Chapter 8

[Ryan R]

Hi all,

 

Thanks in advance for your help. I have been following the chapter 8. I created the database and populated according to this book. Also I put stored procedures into my mysql interface(PHPmyadmin). You can see what I put on below.

--------------------------------------------------------------------------------------------------------------------

DELIMITER $$

CREATE PROCEDURE select_categories (type VARCHAR(6))

BEGIN

IF type = 'coffee' THEN

SELECT * FROM general_coffees ORDER by category;

ELSEIF type = 'other' THEN

SELECT * FROM non_coffee_categories ORDER by category;

END IF;

END$$

DELIMITER ;

 

DELIMITER $$

CREATE PROCEDURE select_products(type VARCHAR(6), cat TINYINT)

BEGIN

IF type = 'coffee' THEN

SELECT gc.description, gc.image, CONCAT("C", sc.id) AS sku,

CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole, sc.price) AS name,

sc.stock

FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id

INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id

WHERE general_coffee_id=cat AND stock>0

ORDER by name ASC;

ELSEIF type = 'other' THEN

SELECT ncc.description AS g_description, ncc.image AS g_image,

CONCAT("O", ncp.id) AS sku, ncp.name, ncp.description, ncp.image,

ncp.price, ncp.stock

FROM non_coffee_products AS ncp INNER JOIN non_coffee_categories AS ncc

ON ncc.id=ncp.non_coffee_category_id

WHERE non_coffee_category_id=cat ORDER by date_created DESC;

END IF;

END$$

DELIMITER ;

 

DELIMITER $$

CREATE PROCEDURE select_sale_items (get_all BOOLEAN)

BEGIN

IF get_all = 1 THEN

SELECT CONCAT("O", ncp.id) AS sku, sa.price AS sale_price, ncc.category, ncp.image, ncp.name, ncp.price, ncp.stock, ncp.description FROM sales AS sa INNER JOIN non_coffee_products AS ncp ON sa.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_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) )

UNION SELECT CONCAT("C", sc.id), sa.price, gc.category, gc.image, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole), sc.price, sc.stock, gc.description FROM sales AS sa INNER JOIN specific_coffees AS sc ON sa.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id WHERE sa.product_type="coffee" AND ((NOW() BETWEEN sa.start_date AND sa.end_date) OR (NOW() > sa.start_date AND sa.end_date IS NULL) );

ELSE

(SELECT CONCAT("O", ncp.id) AS sku, sa.price AS sale_price, ncc.category, ncp.image, ncp.name FROM sales AS sa INNER JOIN non_coffee_products AS ncp ON sa.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_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) UNION (SELECT CONCAT("C", sc.id), sa.price, gc.category, gc.image, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole) FROM sales AS sa INNER JOIN specific_coffees AS sc ON sa.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id WHERE sa.product_type="coffee" 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 ;

 

--------------------------------------------------------------------------------------------------------------------------

 

It executed OK. But when I was trying to invoke it by typing "CALL select_categories('coffee'); The error message (" can't return a result set in the given context. 'coffee') is occurred.

 

Can you help me with this problem? Thanks again and look forward to hearing from you soon.

[HartleySan]

Are you running the CALL function in the following way?

 

$r = mysqli_query($dbc, "CALL select_categories('coffee')");

[Ryan R]

Thanks for reply, Hartley.

 

I haven't tried that one ($r = mysqli_query($dbc, "CALL select_categories('coffee')") yet on php web page.

 

But, the book said you can just call "CALL select_categories('coffee');" in mysql interface(phpmyadmin)... I guess I should try one you recommend on php web page instead of trying to invoke in phpmyadmin.

[Larry]

You can execute a stored procedure from phpMyAdmin, but the support for it is inconsistent, depending upon the version of phpMyAdmin, PHP, and MySQL. I've posted instructions in another forum thread (forget where offhand), but, the short answer is that it's inconsistent and other interfaces are better for this purpose.

[Ryan R]

Thanks Larry, Can you tell me the title of the forum? I have been poking around the forums but don't know which one is what you are talking about.

 

Thanks again for your help. I appreciate it.

[Larry]

I wrote about this in this thread in the old forum: http://www.larryullman.com/forum/read.php?24,50607

[Ryan R]

Thank you very much, Larry. It was very useful info.

 

I have another problem I got into now. I am going to create a new post for the problem. Please find the latest post.

 

Thanks again for your great help.