Jump to content
Larry Ullman's Book Forums

Converting Procedures


Recommended Posts

So I am pretty comfy with PHP and MySQL, but am still learning so I seem to be stuck. I am working on the "Coffee" site and my hosting service doesn't allow EXECUTE for procedures, so I need to convert the procedures into PHP code but just can't seem to get it right. If there is someone who can help I would be ever so greatful. Just for example if I need to convert this code:

 

DELIMITER $$

CREATE PROCEDURE get_order_contents (oid INT)

BEGIN

SELECT oc.quantity, oc.price_per, (oc.quantity*oc.price_per) AS subtotal, ncc.category, ncp.name, o.total, o.shipping

FROM order_contents AS oc INNER JOIN non_coffee_products AS ncp ON oc.product_id=ncp.id

INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id

INNER JOIN orders AS o ON oc.order_id=o.id WHERE oc.product_type="other" AND oc.order_id=oid

UNION SELECT oc.quantity, oc.price_per, (oc.quantity*oc.price_per), gc.category, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole), o.total, o.shipping

FROM order_contents AS oc

INNER JOIN specific_coffees AS sc ON oc.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

INNER JOIN orders AS o ON oc.order_id=o.id

WHERE oc.product_type="coffee" AND oc.order_id=oid;

END$$

DELIMITER ;

 

into PHP script, what would be the easiest way of doing so? I would appreciate any feedback I could get. Thank you in advance!

Johnny

Link to comment
Share on other sites

  • 2 weeks later...

I am having another error issue with a certain procedure. Here is the original code provided

 

 

$r = mysqli_query($dbc, "SELECT CONCAT("O", ncp.id) AS sku, c.quantity, ncc.category, ncp.name, ncp.price, ncp.stock, sales.price AS sale_price

FROM carts AS c INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id

INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id

LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type='other'

AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) )

WHERE c.product_type="other" AND c.user_session_id='$uid'

UNION SELECT CONCAT("C", sc.id), c.quantity, gc.category, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole), sc.price, sc.stock, sales.price

FROM carts AS c INNER JOIN specific_coffees AS sc ON c.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 LEFT OUTER JOIN sales ON (sales.product_id=sc.id

AND sales.product_type='coffee' AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) )

WHERE c.product_type="coffee" AND c.user_session_id='$uid'");

 

I changed the double quotes in the beginning and end to single quotes, pasted below is the code how I changed it. In red is the line where I am receiving the error, If someone can explain I would really appreciate it! Thank you so much!

 

$r = mysqli_query($dbc, 'SELECT CONCAT("O", ncp.id) AS sku, c.quantity, ncc.category, ncp.name, ncp.price, ncp.stock, sales.price AS sale_price

FROM carts AS c INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id

INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id

LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type="other"

AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) )

WHERE c.product_type="other" AND c.user_session_id='$uid'

UNION SELECT CONCAT("C", sc.id), c.quantity, gc.category, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole), sc.price, sc.stock, sales.price

FROM carts AS c INNER JOIN specific_coffees AS sc ON c.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 LEFT OUTER JOIN sales ON (sales.product_id=sc.id

AND sales.product_type="coffee" AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) )

WHERE c.product_type="coffee" AND c.user_session_id='$uid'');

Link to comment
Share on other sites

You're getting an error because of the single quotes around $uid. However, if you use single quotes to enclose the entire string, then you need to concatenate in the $uid value anyway.

 

Why did you want to switch from double to single quotes anyway?

Link to comment
Share on other sites

 Share

×
×
  • Create New...