MySQL Query query - Google Shopping feed

[pb1uk]

Hi all,

Hope all's well. I've been working with PHP and MySQL for a while now, but i just can't get my head around this. I'm writing a script that generates an xml file to be used in a google shopping feed. I have all my products in one table and product options are stored in another table.

Currently i just query the products table for the products for my feed and it works fine. Now i want to include variations. So i need a way to query the products table but for products that do have variations have a separate row returned in the results of the query for each variation. Some won't have any variations though.

So say in my products table i have socks, shirts and shoes and of these i have 2 types of shoe, 3 colours of shirt and just one set of socks it would return 6 rows

• socks

• brown shoes

• black shoes

• red shirt

• green shirt

• blue shirt

I have tried a few things like running separate queries on each table or loops inside loops but they don't seem to do the job properly. I'm sure there's a way i can achieve it in one query or a more effect use of 2 queries, i just need to see the light 🙂

If anyone could help or point me in the right direction it would be a great help.

Thanks in advance

[dalecosp]

The variations are in the "product options" table?

You probably need a JOIN ... inner join, perhaps.

mysql> select * from products;
+----+-------+
| id | name  |
+----+-------+
|  1 | shirt |
|  2 | shoes |
|  3 | socks |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from product_options;
+----+------------+-------+--------+
| id | product_id | name  | value  |
+----+------------+-------+--------+
|  1 |          1 | color | red    |
|  2 |          1 | color | blue   |
|  3 |          1 | color | green  |
|  4 |          3 | color | black  |
|  5 |          3 | color | white  |
|  6 |          2 | color | black  |
|  7 |          2 | color | purple |
+----+------------+-------+--------+
7 rows in set (0.00 sec)

mysql> select distinct po.value, p.name from products p inner join product_options po on p.id=po.product_id;
+--------+-------+
| value  | name  |
+--------+-------+
| red    | shirt |
| blue   | shirt |
| green  | shirt |
| black  | socks |
| white  | socks |
| black  | shoes |
| purple | shoes |
+--------+-------+
7 rows in set (0.00 sec)

[pb1uk]

Hi Dalecosp,

Thanks for your reply. Yes the product variations are stored in a separate table. Through the admin a panel a user can add as many options or variations as they like to a product. I did originally try using a join or inner join, however there's a hitch with that. Products that don't have any variations e.g. a pair of socks in just one colour has a row in the products table but no rows in the variations table. If i join the tables like that then products that have no variations get left out of the resultset.

I basically want to select all products and variations of those products, how to do it correctly is causing me a massive headache! I've tried while looping through the products checking if there are variations the looping through those variations within, but it seems like a long winded way of doing it and left me with some repeated code, so i'm sure there's a better way

[Weedpacket]

pb1uk wrote:

a row in the products table but no rows in the variations table. If i join the tables like that then products that have no variations get left out of the resultset.

So in that case you want a LEFT JOIN.