CODEWITHSUNDEEP
mysqldatabase
Misko
Misko

Reputation: 13

Is it possible to create and use "virtual" table in mysql query?

I have shopping cart and items are stored in session as array. Array key represents id of item, array value is amount of items. array([1]=>1, [2]=>1);

I want to calculate total price. Is it possible to make it using one mysql query?

For example:

SELECT SUM(c.count * i.price) FROM (--virtual table--) c 
                                        LEFT JOIN item i ON i.id=c.id;

Upvotes: 1

Views: 6402

Answers (3)

LINQ Newbee
LINQ Newbee

Reputation: 333

Not possible, since data is in the session (unless you want to write the session data in the DB).
might i suggest this approach 

SELECT i.id, i.price 
FROM item i 
WHERE i.id in (array.keys)

assuming result is placed in a collection,
let itemResult be the collection then iterate

var total = 0
for(var item : itemResult)
  total += item.price * array[i.id]

total would contain the sum using this Pseudocode

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146490

I understand you want this:

SELECT 1 AS product_id, 1 AS quantity
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 100
...

In any case, it looks less awkward to do one of these:

  • Retrieve prices from database
  • Store carts in database

That way you can do all your calculations at a single place.

Upvotes: 1

Tadeck
Tadeck

Reputation: 137410

You are probably referring to Views or subqueries - if so, then you are correct.

Upvotes: 0

Related Questions