Using a foreach/while/loop with sql query

I’m using the following query to create a view. It’s currently only grabbing data from two different tables, subscriptions and subscriptionitems.

For each subscription, I want to grab the item data and output it in the column, the concat function is grabbing one row at the moment and outputting the data in the correct format.

The problem I have is that a subscription can have multiple items, so I need to grab each one and tie it to the correct subscription via the where statement.

How can I do that?

I’ve read about using UNION ALL, is that the right direction to go?

CREATE VIEW Sub_Products AS  
(
    SELECT 
    i.subscription_id as "Subscription ID",
    concat('product_id:',i.product_id,'|quantity:',i.quantity,'|total:',(i.unit_price * i.quantity),'|meta:|tax:0;') as "Products"  
    FROM  subscriptions s, subscriptionitems i, customerdata c  
    WHERE s.id = i.subscription_id
    AND i.active = 1 
);

So as an example of the output – any with the same subscription id should be combined and the products should be output in the same row.

Sample data from the subscriptionitems table:

id	subscription_id	customer_id	product_id	quantity	active	unit_price
556	230	184	262	1	0	2.79
8100	230	184	262	1	1	2.79
555	230	184	260	1	0	2.52

This is my attempt:

CREATE VIEW Sub_Products AS  
(
    SELECT 
    i.subscription_id as "Subscription ID",
    GROUP_CONCAT('product_id:',i.product_id,'|quantity:',i.quantity,'|total:',(i.unit_price * i.quantity),'|meta:|tax:0;') as "Products"  
    FROM  subscriptions s, subscriptionitems i, customerdata c  
    WHERE s.id = i.subscription_id
    AND i.active = 1 
    GROUP BY i.subscription_id
);

Answer

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

If you did so, you would probably notice that there is no JOIN condition for customerdata. In fact, that table is not used at all. And neither is subscriptions.

I would suggest

SELECT i.subscription_id ,
       GROUP_CONCAT('product_id:', i.product_id,
                    '|quantity:', i.quantity,
                    '|total:', (i.unit_price * i.quantity),
                    '|meta:|tax:0;'
                   ) as Products  
FROM subscriptionitems i 
WHERE i.active = 1 ;
GROUP BY i.subscription_id;

Note that I fixed the column names so no escaping is needed either.

Advertisement

Answer