order by question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

order by question

Post by rsmarsha »

I have the following code:

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 />';
					 }
				 }
			}
	   }
}
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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What have you tried with an ORDER BY clause?
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

Well i have the first product ordered by category. I can't see how to get the 2nd to do it as i need to order the exception_id linked to category order as well. Not sure i can do that as the product_id is ordered in that way in the same query. The normal way of ordering by 2 fields won't work as atm the exception_id isn't linked to it's category_order by the products table.
Post Reply