[Resolved] confusion with using GROUP BY

[ali_p]

I have a sql query that pulls out a list of names and categories. I would like to group my list of names by categorys , so that i have
Category one
Allie
Bilko
Jenny

Category two
Rufus
Wayne
Zach

and so on. When i use GROUP BY i only end up with one name in each category. Here is my code

$sql = "SELECT * 
		FROM people
		GROUP BY cisc_category
		ORDER BY last_name ASC";
//execute the sql statement
$result = mysql_query($sql, $conn)or die(mysql_error());
// Set a variable to display results on screen later
$display_block = "";
$count=0;
//will go through each row in the result set and display data
	while ($row = mysql_fetch_array($result)) {
	//give a name to the fields
	$people_code = $row['people_code'];
	$title=$row['title'];
	$first_name = $row['first_name'];
	$last_name = $row['last_name'];
	$cisc_category=$row['cisc_category'];
	$display_people .= "<p class=\"copy\">$cisc_category<br><br><a href=\"people_detail.php?people_code=$people_code\" class=\"newslink\">$last_name, $title $first_name</a></p>
	";

}

Thank you for your time.

[paulnaj]

Hi,

You mis-interpreting the meaning of GROUP BY. You said that you want the records to be 'grouped' like ...

Category one
Allie
Bilko
Jenny
Category two
Rufus
Wayne
Zach

... but the best you'll get is ...

| Category one | Allie |
| Category one | Bilko |
| Category one | Jenny |
| Category two | Rufus |
| Category two | Wayne |
| Category two | Zach  |

... using ORDER BY only ...

$sql = "
SELECT *
FROM people
ORDER BY cisc_category ASC, last_name ASC
";

You usually want GROUP BY when you have aggregate functions in your SELECT clause (COUNT(), MAX() etc.)

Hope that helps.

Paul 🙂

[ali_p]

Thank you for your reply Paul.
Perhaps the only way to achieve what i am after is to just run seperate queries for all my categories, which is bulky as i have about 8 categories to group and list in this way.

Maybe there is a way that php can group and list like this???

[mrhappiness]

Originally posted by ali_p
Maybe there is a way that php can group and list like this???

Yes 🙂

$last_category = 'something realy weird that definitely does not occur in the data you just read';
while ($row = mysql_fetch_array($result)) {
  if ($last_category != $row['cisc_category']) {
    $last_category = $row['cisc_category'];
    echo '<h1>'.$row['cisc_category'].'</h1>';
  }
  echo 'the whole rest of data';
}

[ali_p]

I'm nearly there now, this is the code i have implemented:

$sql = "SELECT * 
		FROM people";
//execute the sql statement
$result = mysql_query($sql, $conn)or die(mysql_error());

$last_category = 'mushroom';
//will go through each row in the result set and display data
	while ($row = mysql_fetch_array($result)) {
	//$cisc_category=$row['cisc_category'];
	//give a name to the fields
	 if ($last_category != $row['cisc_category']) { 
    $last_category = $row['cisc_category']; 
    echo '<strong>'.$row['cisc_category'].'<strong>'; 
  }

$people_code = $row['people_code'];
$title=$row['title'];
$first_name = $row['first_name'];
$last_name = $row['last_name'];
//


echo "<p class=\"copy\"><a href=\"people_detail.php?people_code=$people_code\" class=\"newslink\">$last_name, $title $first_name</a></p>
";

}

But it is still duplicating some categories and it seems to split the people instead of putting all <b>category ones</b> under this heading ,so i may get 4 <b>category one</b> headings each with different number of people under it. This is what i find really weird .

[paulnaj]

Remember the ORDER BY clause, otherwise the records will be displayed in the order that they were entered!

P.

[ali_p]

Thats the ticket! Many many thanks.

Just one more point while i'm on this thread; Is it possible to use sql to direct the order that i want the categories to appear in ?
Say for instance i want category three first then category six and so on. Its not alphabetical so i can't rely on ASC or DESC.

[mrhappiness]

you would have to introduce a sort-column that represents the order in which the entries are to be displayed

[ali_p]

Thats fair enough.
Thanks again for all your replies 🙂