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!