Page 1 of 1

Group By displaying more than one result in group

Posted: Thu Nov 13, 2003 11:58 pm
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?

Posted: Fri Nov 14, 2003 12:29 am
by Paddy
Display your code.

Posted: Fri Nov 14, 2003 1:26 am
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

Posted: Fri Nov 14, 2003 10:22 am
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;

?>

Posted: Fri Nov 14, 2003 11:54 am
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.

Posted: Fri Nov 14, 2003 4:01 pm
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.

Posted: Fri Nov 14, 2003 5:06 pm
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/>"; 
} 
?>

Posted: Fri Nov 14, 2003 11:32 pm
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!