CODEWITHSUNDEEP
phpmysql
Venkat Akula
Venkat Akula

Reputation: 31

execute multiple statements in one mysql query

i need your help. I'm implementing a small ecommerce site and im executing two queries to get the result as follows:

My database table 'cart'

id  itemid  qty   price   customerid
1    item1   1    2500        1 
2    item2   1    11000       1

I need to get 'item1' price, so i'm using following query

SELECT qty, price FROM cart WHERE itemid='item1';

I also need to get sum of all items prices based on customerid so i'm using following one.

SELECT SUM(price) AS totalprice, COUNT(*) AS totalitems FROM cart WHERE customerid='1';

How to get the two results by executing query at once using mysql_query() in php mysql;

Thanks in advance!

Upvotes: 1

Views: 1546

Answers (2)

O. Jones
O. Jones

Reputation: 108841

You could eliminate one of the two roundtrips from php to MySQL by using a UNION ALL operation and getting your data in a couple of rows.

SELECT 'item' name, itemid item, qty, price FROM cart WHERE itemid='item1'
 UNION ALL
SELECT 'count',      customerid, COUNT(*), 0  FROM cart WHERE customerid='1'

Or you could summarize your cart with an aggregate WITH ROLLUP.

SELECT customerid, item, SUM(qty) qty, SUM(price) price, COUNT(*) items
  FROM cart
 GROUP BY customerid, item WITH ROLLUP 
 WHERE customerid ='1'

This will give back a result set that ends with a summary line for the customerid. It may be what you want.

It has to be said: eliminating just one extra php <--> MySQL round trip probably is not worth your development effort.

Upvotes: 1

Sjon
Sjon

Reputation: 5175

If you're only building a small site, there is an ugly workaround you can use; implement a sub-query that includes the sum in each row; like this:

SELECT
    qty, price,
    (SELECT SUM(price) FROM cart WHERE customerid='1') as totalprice,
    (SELECT COUNT(*) FROM cart WHERE customerid='1') AS totalitems
FROM
    cart
WHERE
    itemid='item1';

Upvotes: 0

Related Questions