Re: SELECT question

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: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net

########################################################## # Rick St Jean, # rstjean@internet.look.ca # President of Design Shark, # http://www.designshark.com/, http://www.phpmailer.com/ # Quick Contact: http://www.designshark.com/messaging.ihtml # Tel: 905-684-2952 ##########################################################