Sorting results based on values retrieved from a 'lookup table'

[Benjip]

I'm hoping to get some help with sorting database results based on the results themselves. I have two tables in this example, one being 'product_category_lookup.table' and the other 'products.table'.

The 'product_category_lookup.table' holds data which tells the website which products are associated with which categories. The reason I have a lookup table here rather than just having a 'category' column in 'products.table' is that one product could belong to 7 different categories (think Mens, T-Shirts, Cotton etc.).

My current code is working fine in terms of retrieving the correct data when I view a category, the issue is however that I can currently only sort by using the column names in 'product_category_lookup.table'. This table does not contain any information about the products themselves (other than the products' ID). I want to be able to sort the results based on product_name, which is a column found only in 'products.table'.

So I guess in 'normal speak', I'm wanting to first get all the product_id's that belong to the category that we're viewing (using 'product_category_lookup.table') and then retrieve all the applicable products from 'products.table' (based on the product_id's that we just got), and finally sort these products based on 'product_name' (from products.table) for display/output.

Here's the code I currently have:

// inspect the product_category_lookup table to find out which products are associated with this category

$lookup_result = @mysql_query("SELECT DISTINCT product_id FROM product_category_lookup WHERE category_id='".$category_id."' ORDER BY product_id DESC LIMIT $limit_calculated, $limit");

// turn each of the results from the lookup table into an array so we can use each array to output the details of each product

while ($array = mysql_fetch_array($lookup_result))
{
$data[] = $array[0];
}

// check that there are some products in this category ('if array has something:')

if(is_array($data))
{

// reverse the array so that the latest id (biggest id) is shown first
natsort($data);
$data = array_reverse($data);

// turn each array into a variable called 'value' which we can then use to query the products database for only
// the products associated with this category

foreach ($data as $value) {

$product_grabber = @mysql_query("SELECT * FROM products WHERE id = '$value'");
	
	while ( $row = mysql_fetch_array($product_grabber) ) {
		$product_name = $row['name'];
		$product_id = $row['id'];
		$product_sku = $row['sku'];
		$product_image_1 = $row['image_filename_1'];
		$product_brand = $row['brand'];

echo($product_name);
etc. etc.

Apologies if I haven't explained this well enough or if I'm overlooking something completely basic – I'm still getting my head around arrays etc. so it's hard for me to find the result I'm after as I'm not 100% sure on the lingo (keys, values etc.).

Many thanks in advance!

[as.bhanuprakash]

$product_grabber_query = "SELECT * FROM products WHERE id in (".implode(",", $data).") order by name";
$product_grabber = @mysql_query($product_grabber_query);

I hope the above query helps.

[Benjip]

Thank you so much - that's definitely sorted them in alphabetically order as desired.

However it's now outputting the same product something like 5 times, so it shows all the products alphabetically and then shows them all again.

I tried adding 'DISTINCT' to the $product_grabber_query, as follows:

$product_grabber_query = "SELECT DISTINCT * FROM products WHERE id in (".implode(",", $data).") ORDER BY name";
$product_grabber = @mysql_query($product_grabber_query);

But it's still showing me the same product several times. Do I need another bit of code to trim down the duplicates?

[Benjip]

I've narrowed down my 'duplicate' problem and am thinking it must have something to do with the 'foreach' statement on line 24 of my original post. When I do a print_r($data); to output the contents of the array, there are only 12 product ID's (which is the amount I'd like), yet when the actual data is output there is those 12 products then the same 12 again, etc. - I think it's repeating it 12 times?

[Benjip]

Ok I figured out what was going on - the 'foreach' loop (line 24 of original post) was basically carrying out the SELECT statement (around line 26 of original post but this part of the code changed thanks to as.bhanuprakash's help) several times - in fact as many times as there were 'items/keys' (?) in the array.

In other words, the code was doing this:

"foreach (item/piece of array), SELECT all the products that are within the entire array" - so each 'foreach' loop was pulling all the results of the whole array.

I solved this by adding in a counter/checker which starts at 0, then is increased by 1 every time the foreach loop is run. When it equals 1 (so in other words, after the first loop), the foreach loop stops outputting. Therefore, I don't get multiple results all using the same SELECT statement.

Here's the new code, just in case it helps anyone:

// inspect the product_category_lookup table to find out which products are associated with this category

$lookup_result = @mysql_query("SELECT DISTINCT product_id FROM product_category_lookup WHERE category_id='".$category_id."' ");

// turn each of the results from the lookup table into an array so we can use each array to output the details of each product

while ($array = mysql_fetch_array($lookup_result))
{
$data[] = $array[0];
}

// check that there are some products in this category ('if array has something:')

if(is_array($data)) {

// add a counter/checker so that the 'foreach' loop will only run once,
// thus giving us only one set of results (as desired)
$i=0;

// turn each array into a variable called 'value' which we can then use to query the products database for only
// the products associated with this category
foreach ($data as $value) {

if($i==1) break; 

// implode to find all the product id's contained in the array
// and then grab the products, sorted by product name (name)

$product_grabber_query = "SELECT DISTINCT * FROM products WHERE id in (".implode(",", $data).") ORDER BY name LIMIT $limit_calculated, $limit";
$product_grabber = @mysql_query($product_grabber_query);
	
	while ( $row = mysql_fetch_array($product_grabber) ) {
		$product_name = $row['name'];
		$product_id = $row['id'];
		
echo($product_id); etc. etc.

// 'while' closer (closes the while loop)
}

// add to the $i variable to stop the foreach loop after 1 run
$i++;
	
// 'foreach' closer (closes the foreach loop)
}

// close the 'if array has something' checker

}

[as.bhanuprakash]

Great :)