Lookup table to find ID's which then relate to other tables - with sort?

[Benjip]

I'm stumped with a somewhat complicated 'lookup table' situation. I've tried many different methods to output the data I need (and in the order I need it) but I think I'm just going around in circles.

Basically I'm trying to output a <select> dropdown form element, with serveral different <options> which refer to the available sizes and colours of a garment.

My table layouts are as follows:

colours.table
id, colour_name
1, Black
2, White
3, Black & White tie-dye
4, Grey
5, Beige

(as you can see, these are not stored in alphabetical order as new colours can always be added via the CMS)

sizes.table
id, size_name
1, X-Small
2, Small
3, Medium

These are not stored in any particular order either, as new sizes can be added (6, One Size Fits All, 8, X-Large etc.) at any time.

product_size_colour_lookup.table
id, product_id, colour_id, size_id, quantity
1, 3, 1, 2, 4

In this example, the product id is '3'. The Colour is '1' (Black, taken from colours.table), the Size is '2' (Small, taken from sizes.table) and there are 4 units of this size/colour combo instock.

I hope I've done the right thing in storing the sizes and colours in their own tables for expansion's sake and the flexibility of being able to edit a colour name (due to a typo etc.) or add a new colour to the database if a new product in an unusual colour is added.

Now onto the issue: I'm fine with outputting the above 'combos' of size & colour, as <option>'s on the Product Detail page. So my code currently outputs <option value="1,2">Black, Small</option>. The problem is, if I then add to the lookup table a row that results in <option value="5,2">Beige, Small</option>, it sits below the 'Black, Small' option which is not ideal as this is not alphabetical (Beige should come before Black). Ideally the results would be sorted using both Colour and Size - so '"Black, 12", "Black, 8", "Beige, 10"' would become '"Beige, 10", "Black, 8", "Black, 12"'.

Here's the snippet in question ($product_id is defined at the beginning of the Product Detail page):

$colour_size_lookup = @mysql_query('SELECT * FROM product_size_colour_lookup WHERE product_id="'.$product_id.'" AND quantity>"0"');
		
	while ( $row = mysql_fetch_array($colour_size_lookup) ) {
		$colour_id = $row['colour_id'];
		$size_id = $row['size_id'];
	
			$colour_lookup = @mysql_query('SELECT * FROM colours WHERE id="'.$colour_id.'" ORDER BY colour_name');
				while ( $row = mysql_fetch_array($colour_lookup) ) {
				$colour_id = $row['id'];
				$colour_name = $row['colour_name'];
			}
			
			$size_lookup = @mysql_query('SELECT * FROM sizes WHERE id="'.$size_id.'"');
				while ( $row = mysql_fetch_array($size_lookup) ) {
				$size_id = $row['id'];
				$size_name = $row['size_name'];
			}
			
			$combo = array('Colour_name' => $colour_name, 'Size_name' => $size_name, 'Colour_id' => $colour_id, 'Size_id' => $size_id);
				
				natsort($combo);
		
				list($Colour_name, $Size_name, $Colour_id, $Size_id) = $combo;
				
				print_r('<option value="'.$combo[Colour_id].','.$combo[Size_id].'">'.$combo[Colour_name].', ');
				print_r('Size '.$combo[Size_name].'</option>');
			}

As you can see, I tried adding in a 'natsort' (tried most of the sorting functions) but I figure this doesn't work as it's only processing the arrays one at a time, thus it's not getting a big list of them which it can then sort.

Any guidance or a point in the right direction would be hugely appreciated.

[darkagn]

I would do one query with two joins rather than the three queries you currently have. This allows you to sort on any column from any of the three tables as needed. Something like this would work:

-- select all appropriate columns from the three tables
select p.id, p.product_id, p.colour_id, p.size_id, p.quantity, c.colour_name, s.size_name
-- from the main table
from product_size_colour_lookup p
-- joined to another table on its id
inner join colours c on p.colour_id = c.id
-- and joined to another table on its id
inner join sizes s on p.size_id = s.id
-- now set the sorting however you want
order by c.colour_name asc, s.size_name asc

You may need to modify the query to suit your needs, but my comments should explain what I have done. Please re-post if I have confused you.

[Benjip]

I would do one query with two joins rather than the three queries you currently have. This allows you to sort on any column from any of the three tables as needed. Something like this would work:

Thank you so much darkagn, that worked perfectly!

I still have the other two SELECT queries going which are working – would you be able to demonstrate how I would streamline the code to only use the one SELECT?

I imagine it's something like:

while ( $row = mysql_fetch_array($colour_size_lookup) ) {
$colour_id = $row;
$size_id = $row;
}

[darkagn]

Yes, except that I don't think you need the p in the $row array. The one query should replace all of the queries you previously had, and to get the colour_id (for example) you just go:

while ( $row = mysql_fetch_array($colour_size_lookup) ) {
   $colour_id = $row['colour_id'];
   // ...
}