Page 1 of 1

MySQL Query Error Message

Posted: Sat Jul 19, 2008 5:33 pm
by Wendell
Hello,

First of all, thank you in advance to anyone who can help me out. I'm very new to PHP (and this forum) and have run through several simple tutorials from various sites. Most of them, I have found, are based on PHP4/MySQL4. The last tutorial I used helped me create a simple shopping cart and online store, and that's where I am befuddled. My host is currently running PHP5/MySQL5.

Everything seems to be working great, except for one page that displays customer orders. The error I get in the browser is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order o, order_item oi, product p WHERE oi.prod_id = p.prod_id AND o.order_id' at line 3

Well, I hunted down the respective code and found it to be as such:

Code: Select all

$sql = "SELECT o.order_id, o.order_shipping_first_name, order_shipping_last_name, order_date, order_status,
  SUM(prod_price * order_qty) + order_shipping_cost AS order_amount
  FROM order o, order_item oi, product p 
  WHERE oi.prod_id = p.prod_id
  AND o.order_id = oi.order_id $sql2
  GROUP BY order_id
  ORDER BY order_id DESC";
$result = dbQuery(getPagingQuery($sql, $rowsPerPage));
$pagingLink = getPagingLink($sql, $rowsPerPage, $queryString);
After doing some research online (including searching this forum), I found some suggestions to use LEFT JOIN. However, those suggestions were in response to simpler queries and I am trying to retrieve data from multiple tables. I tried to make the suggestions work in the code above, but I can't seem to figure out the proper syntax. I'm not exactly sure what is wrong and I don't know the exact place in the code where the problem really lies.

What I'm really hoping for here is for someone to show me how it should be done but also to explain why. I'm really trying hard to learn PHP and I would like to know the underlying reasons and not just receive free code. :)

Thanks again for any help I can get.

Re: MySQL Query Error Message

Posted: Sat Jul 19, 2008 9:18 pm
by EverLearning
I think there should be parenthesis around

Code: Select all

SUM(prod_price * order_qty) + order_shipping_cost
so your query would look like

Code: Select all

$sql = "SELECT o.order_id, o.order_shipping_first_name, order_shipping_last_name, order_date, order_status,
(SUM(prod_price * order_qty) + order_shipping_cost) AS order_amount
FROM order o, order_item oi, product p
WHERE oi.prod_id = p.prod_id
AND o.order_id = oi.order_id $sql2
GROUP BY order_id
ORDER BY order_id DESC";
If you need help rewriting your query, feel free to ask.