5

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

view full story
linux-howto

http://www.daniweb.com – 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! (General)