[SOLVED] SORT ALPHABETICALLY WITH ALPHA AS HEADER

[eightFX]

Good Evening,

I am trying to sort a query of names alphabetically which I can do. The issue is that I need to group them by the first letter of the names. Basically I need my output to look like this:

A
Ambrose, Melanie
Austin, Jay

B
Bailey, Brandon
Beam, Jim

... etc.

What I tried doing is this:

[code]
$alphaQuery = mysql_query("SELECT lname FROM names ORDER BY lname");
       
while($alphaResult = mysql_fetch_array($alphaQuery)) {

$limitAlpha = substr($alphaResult[lname],0,1);

echo ' - '. $limitAlpha .' - ';
 
$Alpha = $alphaResult[R_lname];

$namesQuery = mysql_query("SELECT * FROM names WHERE lname LIKE '$Alpha%' ORDER BY lname");

while($namesResult = mysql_fetch_array($namesQuery)) {

echo '<br>'. $namesResult[lname] .', '. $namesResult[fname] .'';

} // CLOSE namesResult WHILE

  } //CLOSE alphaResult WHILE
[/code]

The above code prints out what I want but duplicates it so the out put looks like:

B
Bailey, Brandon
Beam, Jim

B
Bailey, Brandon
Beam, Jim

For as many names that start with the same letter. Any help appreciated, thanks!

[eightFX]

I think what I need to do is select distinct values in the first query for just the first letter of lname but I do not know if that is possible...Thanks.

[craygo]

[code]<?php
// Set initial group values
$letter = '';
// Query database
$sql = "SELECT SUBSTRING(lname, 1, 1) AS letter, names FROM items ORDER BY letter, names";
  $res = mysql_query($sql);
  $num_rows = mysql_num_rows($res);
if($num_rows > 1){
// Set initial row color
$bgcolor = "FFFFFF";
    while ($rows = mysql_fetch_assoc($res)){
// Print Group headers
if ($rows['letter'] != $letter) {
// Print Group Name
print '<table width="500" align="center" cellspacing=0 cellpadding=0>';
print '<tr bgcolor=83EA44>';
print '<td><strong>'. strtoupper($rows['letter']).'</strong></td>';
print '</tr>';
print '</table>';
}
// Alternate row color
if ($bgcolor == "#E0E0E0"){
  $bgcolor = "#FFFFFF";
} else {
  $bgcolor = "#E0E0E0";
}
// Print Database Details
print '<table width=500 align=center cellspacing=0>';
print '<tr bgcolor='.$bgcolor.'>';
print '<td width=20 align=center>&nbsp;</td>';
print '<td width=400 align=center>'.$rows[names].'</td>';
print '</tr>';
// Reset group values
$letter = $rows['letter'];
}
} else {
// Print No data message
print '<table width=650 align=center>';
print '<tr>';
print '<td align=center><strong>NO ITEMS!!</strong></td>';
print '</tr>';
print '</table>';
}
?>[/code]
Ray

[eightFX]

Thanks craygo! I guess I was not even close! Works like a champ!