MySQL Query Error Message

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
Wendell
Forum Newbie
Posts: 1
Joined: Sat Jul 19, 2008 5:08 pm

MySQL Query Error Message

Post 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.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: MySQL Query Error Message

Post 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.
Post Reply