>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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
