PHP, MySQL LEFT JOINS etc
I have some MySQL tables. The relevant ones are:
COUNTRIES:
id, country
PRODUCTS:
prodcode, prodname, catid
STOCK:
id, prodcode, countryid, stockamt, lastupdate
At the moment, I am using the following query:
And getting the following results:
However, I would like to get something more like:
I have tried all sorts of GROUP BY queries, UNIONs, inserting the result into a multidimensional array and piecing it back together again and I just cannot figure out for the life of me how to make this work. Should I be working on my MySQL query, my presentational code, should I be changing the structure of my DB? Any help you can give would be wonderful.. and please try and make it relatively idiot proof as I've been on this for about 4 hours and my brains hurt. Thanks :)
Update: based on the awesome help I got I've found this which seems to be the most friendly explanation of what I want to do. Far too tired to do it now though, so I'll update again tomorrow with my solution (for anyone that is interested) :)
COUNTRIES:
id, country
PRODUCTS:
prodcode, prodname, catid
STOCK:
id, prodcode, countryid, stockamt, lastupdate
At the moment, I am using the following query:
SELECT `stock`.`stockamt`, `products`.`prodcode`, `countries`.`country` FROM `stock` LEFT JOIN `products` ON `stock`.`prodcode` = `products`.`prodcode` LEFT JOIN `countries` ON `stock`.`countryid` = `countries`.`id`And getting the following results:
| Product Code | Stock Amount | Country |
|---|---|---|
| AMA-N-SW | 324 | UK |
| AMA-N-SW | 546 | Australia |
| AMA-N-SW | 11 | USA |
| BOOBS | 123 | UK |
However, I would like to get something more like:
| UK | Australia | USA | |
|---|---|---|---|
| AMA-N-SW | 324 | 546 | 11 |
| BOOBS | 11 | 0 | 0 |
I have tried all sorts of GROUP BY queries, UNIONs, inserting the result into a multidimensional array and piecing it back together again and I just cannot figure out for the life of me how to make this work. Should I be working on my MySQL query, my presentational code, should I be changing the structure of my DB? Any help you can give would be wonderful.. and please try and make it relatively idiot proof as I've been on this for about 4 hours and my brains hurt. Thanks :)
Update: based on the awesome help I got I've found this which seems to be the most friendly explanation of what I want to do. Far too tired to do it now though, so I'll update again tomorrow with my solution (for anyone that is interested) :)
