Group By displaying more than one result in group

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
THEMADGEEK
Forum Newbie
Posts: 21
Joined: Thu Oct 30, 2003 6:04 pm

Group By displaying more than one result in group

Post by THEMADGEEK »

I am attempting to use GROUP BY and get all the records in each group to display under the group heading. Currently it only displays the first record.

What am I doing wrong?
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post by Paddy »

Display your code.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

could be your query, or your php code.

here is a sample query ( assuming you have the field HOUR )

Code: Select all

<?
$sql = "select field_name, hour(time) FROM db_name group by hour(time) order by hour(time) ASC"
$result = mysql_query($sql) or die(MySQL_Error());
?>
otherwise, it may just be how you are trying to retrieve your data, which should be in the format like so :

Code: Select all

<?php

$sql = "select field_name, hour(time) FROM db_name group by hour(time) order by hour(time) ASC"
$result = mysql_query($sql) or die(MySQL_Error());
$num_results = mysql_num_rows($result); 
$row = mysql_fetch_assoc($result); 


while ($row = mysql_fetch_assoc ($result))
	{ 
		$field_name = $row['field_name];
		echo $field_name;
	}
?>
Hope that helps
THEMADGEEK
Forum Newbie
Posts: 21
Joined: Thu Oct 30, 2003 6:04 pm

Post by THEMADGEEK »

OK Here's my code; what I get is only the first item in the group displays like in group 1 I have more than one item but only the first one in the group shows up.

Code: Select all

<?

$sql = "SELECT * FROM table WHERE category = '$cat' GROUP BY subcat";

$result = @mysql_query($sql,$connection) or die("Couldn't execute query.");

while ($row = mysql_fetch_array($result))

&#123;

$subcat = $row&#1111;'subcat'];
$name = $row&#1111;'name'];
$url = $row&#1111;'url'];

	$display_block .= "
        $subcat
        <a href="http://$url">$name</a>";
&#125;

?>
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Yeah, it select 1 row per group because it's a purpose of GROUP BY clause.

Tell us what do you expect to see using this code.
THEMADGEEK
Forum Newbie
Posts: 21
Joined: Thu Oct 30, 2003 6:04 pm

Post by THEMADGEEK »

I have lists that are categorized and I want to see the category and all the items in the list (matching the category) below it. I guess like this.

CATEGORY 1
item 1
item 2
item 3
etc...

CATEGORY 2
item 1
item 2
item 3
etc...

etc... etc... etc...

for as many categories and item that there are.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

<?php
$sql = "SELECT * FROM table WHERE category = '$cat' ORDER BY subcat"; 
$result = @mysql_query($sql,$connection) or die("Couldn't execute query."); 

$was_subcat=""; //assuming you haven't empty subcats
while ($row = mysql_fetch_array($result)) { 
   if($row['subcat']!=$was_subcat) //don't display if already displayed
       @$display_block.= "<h3>{$row['subcat']}</h3>";
   $was_subcat=$row['subcat'];

   @$display_block .= "<a href="http://{$row['url']}">$row['name']</a><br/>"; 
} 
?>
THEMADGEEK
Forum Newbie
Posts: 21
Joined: Thu Oct 30, 2003 6:04 pm

Post by THEMADGEEK »

I had to play with it a bit but I got it in the end. Here's what I ended up with:

Code: Select all

<?
$sql = "SELECT * FROM table WHERE category = '$cat' ORDER BY subcat"; 

$result = @mysql_query($sql,$connection) or die("Couldn't execute query."); 

$was_subcat=""; //assuming you haven't empty subcats 

while ($row = mysql_fetch_array($result)) &#123; 

$category = $row&#1111;'category'];
$subcat = $row&#1111;'subcat'];
$name = $row&#1111;'name'];
$url = $row&#1111;'url'];

   if($row&#1111;'subcat']!=$was_subcat) //don't display if already displayed 
   
       @$display_block .= "<br/>$subcat<br/>"; 
	   
   $was_subcat=$row&#1111;'subcat']; 

   @$display_block .= "<a href="http://$url">$name</a><br/>"; 
&#125; 
?>
Thanks so much for all the help!
Post Reply