Group By displaying more than one result in group
Moderator: General Moderators
-
THEMADGEEK
- Forum Newbie
- Posts: 21
- Joined: Thu Oct 30, 2003 6:04 pm
Group By displaying more than one result in group
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?
What am I doing wrong?
could be your query, or your php code.
here is a sample query ( assuming you have the field HOUR )
otherwise, it may just be how you are trying to retrieve your data, which should be in the format like so :
Hope that helps
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());
?>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;
}
?>-
THEMADGEEK
- Forum Newbie
- Posts: 21
- Joined: Thu Oct 30, 2003 6:04 pm
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>";
}
?>-
THEMADGEEK
- Forum Newbie
- Posts: 21
- Joined: Thu Oct 30, 2003 6:04 pm
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
I had to play with it a bit but I got it in the end. Here's what I ended up with:
Thanks so much for all the help!
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/>";
}
?>