Hi All,
I'm building a standard shopping cart style e-commerce site using PHP and MySQL running on Apache.
I store my users' cart info in this table:
+------------+--------------+------+-----+---------+-------+ |
| Field | Type | Null | Key | Default | Extra | +
+------------+--------------+------+-----+---------+-------+ |
| custId | int(11) | | | 0 | | |
| itemId | int(11) | YES | | NULL | | |
| qty | int(11) | YES | | NULL | | |
| totalPrice | float(10,2) | YES | | NULL | | |
| dateAdded | timestamp(6) | YES | | NULL | | +
+------------+--------------+------+-----+---------+-------+
I currently use this statement to display a user's cart contents:
SELECT items.itemId, description, link, qty, price FROM carts, items WHERE carts.custId = '$custId' AND items.itemId = carts.itemId
If a user happens to add the same item to their cart more than once, this statement displays the item more then once. Is there a way I can augment the select statement above so I can group multiple instances of the same product into a single line, but still get a sum of the quantities so the single lines reflects the total quantity of all the instances. So for example, if add 2 of itemId 1 and then add 3 more of itemId 1 my cart will display itemId 1 two times ... once with a qty of 2 and once with a qty of 3. Instead, I would like it to display one time with a qty of 5.
Make sense? I'm sure I could hack around this, but I'd like to know if it is doable in a single select statement.
Thanks!
Nick
Sir, use GROUP BY and an aggregate function.
SELECT items.itemId, description, link, Sum(qty) AS sum_qty, price
FROM carts, items
WHERE carts.custId = '$custId' AND items.itemId = carts.itemId
GROUP BY itemId;
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database