Page 1 of 1

Question about group by, and moving displaying results.

Posted: Fri Dec 02, 2005 3:18 am
by rsmarsha
I have a 3 table query which is pulling out products based on category, product id and page variable (page they appear in).

If i used group by would it be possible to make a page loop through the categories in seperate drop down lists?

Example if i had 2 categories such as cpu's and motherboards' could i use group by to group all the cpu's together and then loop through those in one select list. After that also make it group through motherboard's in a list?

It's probably really simple, but my heads spinning with other things atm. :) A point in the right direction would be great.

Thanks in advance.

Posted: Fri Dec 02, 2005 4:37 am
by Chris Corbyn
Moved to databases...

It sounds like you're perhaps hoping GROUP BY behaves differently. I'd just use a WHERE clause to get items of a given category. If your database is set up nicely you'll have table for the categories such as:

Code: Select all

id   |     category
-------------------------
 1  |  Motherboards
 2  |  Hard Drives
 3  | CPUs
So you should be able to filter using those.

GROUP BY actually groups things together into a *single* row, it's most useful when doing calculations on data such as counting rows or adding values up. But to show it's basic output you could have a table like so:

Code: Select all

id   |  color
----------------
 1   |   Red
 2   |   Blue
 3   |   Orange
 4   |   Pink
 5   |   Orange
As you can see there are two rows with "orange" as the color.

Code: Select all

SELECT * FROM tbl_name GROUP BY color
Would output

Code: Select all

id   |  color
----------------
 1   |   Red
 2   |   Blue
 5   |   Orange
 4   |   Pink
Notice the duplicate colors got combined into one row? :D

Posted: Fri Dec 02, 2005 5:04 am
by rsmarsha
Thanks, i know how group by works i think. :) I've used it before combined with a count to group as you showed and count the numbers for each grouped result.

I know i can do what i want to do by having a query for each category, but was wondering if there was a way to reduce the number of queries.

I think i'll end up having 1 query per cat of products i want to pull out, then looping through the results as normal. No harm in asking for alternatives though. :)

Posted: Fri Dec 02, 2005 10:07 am
by pickle
You could do one query and ORDER BY the category. Then, when you're looping through the results, just break up the results where the category changes:

Code: Select all

$result = ...
while($row = mysql_fetch_assoc($result))
{
   $formatted_results[$row['category']][] = $row;
}
//$formatted_results is now a nice array

foreach($formatted_results as $category=>$entries)
{
  echo "<select>";
  foreach($entries as $curr_entry)
  {
     //whatever you want to put in your list
  }
  echo "</select>";
}