#This is my SQL which produces the result below which is great...
 
SELECT pi.product_name product_name, product_specs.product_ID, product_specs.p_comp, product_specs.p_s_comp, product_specs.price
FROM product_specs
LEFT JOIN product_item pi ON pi.ID = product_specs.product_ID
WHERE product_specs.product_ID =3
AND product_specs.show_on_website != '1'
AND product_specs.live = '1'
ORDER BY product_specs.p_comp ASC , product_specs.price ASC;
 
 
# the result:
 
 
ID 	product_name 	product_ID 	p_comp 			p_s_comp 	price 
18	BPB Aquastop	3		Sheet Size 1200 x 10mm	x 2400mm l	29.9
19	BPB Aquastop	3		Sheet Size 1200 x 10mm	x 2700mm l	33.9
20	BPB Aquastop	3		Sheet Size 1200 x 10mm	x 3000mm l	36.9
21	BPB Aquastop	3		Sheet Size 1200 x 10mm	x 3600mm l	43.9
22	BPB Aquastop	3		Sheet Size 1200 x 13mm	x 2400mm l	36.9
23	BPB Aquastop	3		Sheet Size 1200 x 13mm	x 2700mm l	41.4
24	BPB Aquastop	3		Sheet Size 1200 x 13mm	x 3000mm l	45.9
 
# each product would sit in my data structure like so:
# this is where it gets hard - there is only 2 real products in this result, they are split by the p_comp column, then there is different 
# lengths of each of those 2 products which also have different prices, this is the p_s_comp and price fields which can easily be 
# joined as one column... the end result is in the website, there will be 2 items ina  table displayed and each item has a drop down 
# list which shows the different lengths/prices. So I figured if I could get this data into a data structure like below, then I would be 
# in business ;) make sense?
 
# also the result needs to be in price order of lowest to highest in each of the sub components, if I use a hash it may not work so array to keep the order could be better, but below I have used a hash...
 
#(note the hash in the sub_components part below...) 
# also I don't really know how to have multiple anonymous hashes or arrays, I just guessed it would be like $products = { ... } { ... } { ... };
 
$products = {
  ID=> 3,
  name=> BPB Aquastop,
  component=> Sheet Size 1200 x 10mm,#this is actually what groups them together - if these are the same then it should be in the same group or product which will end up in the right drop down list...
  sub_components=> {18=>"x 2400mm l 29.9", 19=>"x 2700mm l 33.9", 20=>"x 3000mm l 36.9", 21=>"x 3600mm l 43.9"},
} { 
  ID=> 3,
  name= BPB Aquastop,
  component=> Sheet Size 1200 x 13mm, 
  sub_components=> {22=>"x 2400mm l 36.9", 23=>"x 2700mm l 41.4", 24=>"x 3000mm l 45.9"}, # this structure needs to have the order remain intact so should be an array somehow.. I'd prefer not to use a hash tie method to order the hash...
}