If you have a shopping cart.... why would you want them to add the same
item in there twice... why not just have them update their quantity to 5?
At 11:10 PM 5/16/01, you wrote:
>>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]
##########################################################
# Rick St Jean,
# [EMAIL PROTECTED]
# President of Design Shark,
# http://www.designshark.com/, http://www.phpmailer.com/
# Quick Contact: http://www.designshark.com/messaging.ihtml
# Tel: 905-684-2952
##########################################################
--
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]