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?

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>";
}