CODEWITHSUNDEEP
phpmysql
user3625176
user3625176

Reputation: 73

php shopping cart -group by selection and get the total

My question is a little bit long...

This is the is the first picture for my question:

enter image description here

---Here it is the query how I make the selection:

$costumer_name=$_SESSION['p_user'];
$date=date("y-m-d");

$query_8="select * from orders
        where costumer_name='$costumer_name' && date='$date'
        order by product_id desc";
$result_8=mysql_query($query_8);

---As you see, the selection table needs a group by query and here is that query and the result:

$costumer_name=$_SESSION['p_user'];
$date=date("y-m-d");

$query_8="select * from orders
        where costumer_name='$costumer_name' && date='$date'
        group by product_id desc";
$result_8=mysql_query($query_8);

enter image description here

---Here it is how I get the SUM:

$query_9="select SUM(totali) as totali from orders where costumer_name='$costumer_name' && date='$date'";
$result_9=mysql_query($query_9);

$row_9=mysql_fetch_array($result_9);

$totali=$row_9['totali'];

---And finaly the problem:

1.How to count the number of orders with the same product_id(sasia)? 2.Get the first total of -cmimi and -sasia? 3.Get the final total?

Thank you

Upvotes: 1

Views: 394

Answers (1)

Aleksandar Miladinovic
Aleksandar Miladinovic

Reputation: 1027

first of all i think there is better solution then every time when somebody click add to cart button to always insert new row in the database. If product is already there you should update quantity by +1, that way you will have only one row of one products instead of 3. I don't know exactly how your database look but here is an idea how you could do that:

CREATE PROCEDURE add_to_cart(IN in_cart_id INT, IN in_product_id...)
BEGIN 
   DECLARE quant INT;

   SELECT quantity
   FROM cart
   WHERE cart_id = in_cart_id
     AND product_id = in_product_id
   INTO quant;

   IF quant IS NULL then
       INSERT INTO cart....
   ELSE
       UPDATE cart
       SET quantity = quantity + 1
       WHERE cart_id = in_cart_id
         AND product_id = in_product_id;
   END IF;
   END$$

I think that is much better solution but if you want to do that the way you started then here is a way to do that:

 SELECT productId, name, price, COUNT(quantity) as quantity, (price *       COUNT(quantity)) as total
 FROM where-ever-you store data
 WHERE expression 
 GROUP BY productId;

that is answer on two your question, for the third total sum you need to write separate SELECT statement

SELECT SUM(price * quantity) as total_sum
FROM where-ever-you-store-data
WHERE expression if needed...

Upvotes: 1

Related Questions