SELECT question
| From: | Dan Fitzpatrick | Date: | Thu, 17 May 2001 16:55:39 +0000 |
| Subject: | SELECT question | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-9412@lists.php.net to get a copy of this message | ||
Nick,
Use the group by function:
SELECT items.itemId, description, link, sum(qty) AS total_qty,
sum(price) AS total_price FROM carts, items
WHERE carts.custId = '$custId' AND items.itemId = carts.itemId
GROUP BY items.itemId, description, link
You'll have to change the reference to the following 2 variables in your
PHP code:
$qty >> $total_qty
$price >> $total_price
Dan
>Subject: SELECT question
> Date: Wed, 16 May 2001 21:06:56 -0400
> From: "Nicholas W. Miller" <nick@webzeit.com>
> To: php-db@lists.php.net
>
>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