JBruner Posted June 12, 2012 Share Posted June 12, 2012 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 More sharing options...
JBruner Posted June 12, 2012 Author Share Posted June 12, 2012 I actually scoured your site and have found the articles in which you wrote the code, Thank you very much!! If I have any issues I will post back later. Link to comment Share on other sites More sharing options...
JBruner Posted June 25, 2012 Author Share Posted June 25, 2012 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 More sharing options...
Larry Posted June 25, 2012 Share Posted June 25, 2012 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 More sharing options...
Recommended Posts