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))
{
$subcat = $rowї'subcat'];
$name = $rowї'name'];
$url = $rowї'url'];
$display_block .= "
$subcat
<a href="http://$url">$name</a>";
}
?>
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)) {
$category = $rowї'category'];
$subcat = $rowї'subcat'];
$name = $rowї'name'];
$url = $rowї'url'];
if($rowї'subcat']!=$was_subcat) //don't display if already displayed
@$display_block .= "<br/>$subcat<br/>";
$was_subcat=$rowї'subcat'];
@$display_block .= "<a href="http://$url">$name</a><br/>";
}
?>
Thanks so much for all the help!