order by question
Posted: Thu Aug 17, 2006 2:47 am
I have the following code:
This shows exceptions in the form :
product name (id is products.product_id), is not compatible with product name (id is $er[exception_id]).
The first query exception_search is the one i'm having trouble with. The query itself works fine and orders resulting exceptions by the category_order linked to $postValue.
Say i have 2 $postValues with their category id's as 1 and 2, then it will order the results by showing all exceptions with 1 first and then 2 which is fine. The problem is for say products with cat id 1 there might be 3 exception products.
Example:
2 products have exceptions
product 1 (cat1) exception is from cat 1
product 1 (cat1) exception is from cat 3
product 1 (cat1) exception is from cat 2
product 2 (cat2) exception is from cat 1
At present it shows the first part (product1 or 2) in order of the cat_order for those. The problem is the exceptions are shown in the order they were entered into the database and as such their categories are not in order. I want to have it like this :
product 1 (cat1) exception is from cat 1
product 1 (cat1) exception is from cat 2
product 1 (cat1) exception is from cat 3
product 2 (cat2) exception is from cat 1
Hope i've explained it clearing enough, it's a bit confusing, hehe.
Code: Select all
if (!empty($_POST))
{
$count == 0;
foreach ($_POST as $postName => $postValue)
{
$exception_search = "SELECT * FROM exceptions,products LEFT JOIN categories ON products.category_id=categories.category_id WHERE products.product_id=exceptions.product_id AND products.product_id='$postValue' ORDER BY category_order ASC";
$eq = mysql_query($exception_search) or die("Query $exception_search Failed".mysql_error());
while ($er = mysql_fetch_assoc($eq))
{
foreach ($_POST as $postName => $postValue)
{
if ($er['exception_id']==$postValue)
{
$count ++;
$find = "SELECT description FROM products,exceptions WHERE products.product_id=exceptions.exception_id AND exceptions.exception_id='$er[exception_id]'";
$findq = mysql_query($find) or die("Query $find Failed".mysql_error());
$fr = mysql_fetch_assoc($findq);
echo 'Product '.$er['description'].' is not compatible with '.$fr['description'].'<br />';
}
}
}
}
}product name (id is products.product_id), is not compatible with product name (id is $er[exception_id]).
The first query exception_search is the one i'm having trouble with. The query itself works fine and orders resulting exceptions by the category_order linked to $postValue.
Say i have 2 $postValues with their category id's as 1 and 2, then it will order the results by showing all exceptions with 1 first and then 2 which is fine. The problem is for say products with cat id 1 there might be 3 exception products.
Example:
2 products have exceptions
product 1 (cat1) exception is from cat 1
product 1 (cat1) exception is from cat 3
product 1 (cat1) exception is from cat 2
product 2 (cat2) exception is from cat 1
At present it shows the first part (product1 or 2) in order of the cat_order for those. The problem is the exceptions are shown in the order they were entered into the database and as such their categories are not in order. I want to have it like this :
product 1 (cat1) exception is from cat 1
product 1 (cat1) exception is from cat 2
product 1 (cat1) exception is from cat 3
product 2 (cat2) exception is from cat 1
Hope i've explained it clearing enough, it's a bit confusing, hehe.