PHP & MySQL Group By

So I'm having trouble with the GROUP BY query in MySQL. What I want is it to group all fields that have the same title, and then display the other fields with that same group. For example, my table is set up like so:

[U]ID[/U]   [U]title[/U]              [U]name[/U]              [U]abbrev[/U]
1  Bank Loan          Bob's Account         BOB
2  Bank Loan          Wells Fargo           WF
3  Scholarship        Alex's Account        ALEX
4  Scholarship        University Treasury   UT
5  Lottery Winnings   Dan's Account         DAN
6  Lottery Winnings   National Treasury     USANT
7  Lottery Winnings   Bank of America       BAM

Code (markup):

What I want to do is output the title once, then have the name and abbrev fields each listed under that one title. Is that possible?

Here's what I've got so far, but it's only giving me one result from the query and not all of them:

	$i = 0;
$query2 = "SELECT * FROM transactionitems GROUP BY title ORDER BY title ASC";
$query3 = "SELECT *, COUNT(title) FROM transactionitems GROUP BY title ORDER BY title ASC";

$results2 = mysql_query($query2) or die(mysql_error());
$results3 = mysql_query($query3) or die(mysql_error());
					
$num2 = mysql_num_rows($results2);
$num3 = mysql_num_rows($results3);
					
while($i < $num3){
   $ttitle = mysql_result($results3,$i,'title');
   echo '<tr><td><b>'.$ttitle.'</b></td><td>&nbsp;</td><td>&nbsp;</td></tr>';
   echo "\n\t\t\t\t\t";
   $aname = mysql_result($results2,$i,'name');
   $aabr = mysql_result($results2,$i,'abbrev');
   echo '<tr><td>'.$aname.'</td><td>'.$aabr.'</td><td>&nbsp;</td></tr>';
   echo "\n\t\t\t\t\t";
   $i++;
}

PHP:

 

It's not possible to do it like that no. What you need to do is just ORDER BY `name`. That way they'll all list together and then you just need to set up a temp variable in your fetch loop to remember the last title so you know if you've already output that title, and not output it again

 

Do you think you could show me an example of how to go about doing that?

 

I'm not sure if this is the best way, but it should work:

<?php

//Get a list of each unique title
$result_titles = mysql_query( "SELECT title FROM transactionitems GROUP BY title ORDER BY title ASC" );

//Loop through each title, output the current title
while( $row = mysql_fetch_array( $result_titles )) {
     $title = $row['title'];

     echo '<tr><td><b>'.$title.'</b></td><td>&nbsp;</td><td>&nbsp;</td></tr>';
     echo "\n\t\t\t\t\t";

     //Get the name and abbrev columns for all rows with the current title
     $result_name_abbrev = mysql_query( "SELECT name, abbrev FROM transactionitems WHERE title='$title'" );

     //Loop through each row, output name and abbrev
     while( $row_name_abbrev = mysql_fetch_array( $result_name_abbrev )) {

     echo '<tr><td>'.$row_name_abbrev['name'].'</td><td>'.$row_name_abbrev['abbrev'].'</td><td>&nbsp;</td></tr>';
     echo "\n\t\t\t\t\t";

     }
}

?>

Code (markup):

 

You can do it using group_concat in following manner:

select title, group_concat(name), group_concat(abbrev) from transactionitems group by title

hope it helps you reduce multiple queries..