help still needed w/sorting problem

claire

I have a MySQL db-table as follows:

apt no | name
--------|--------
1 | Smith
1 | Smith
2 | Doe
3 | Brown
3 | Larsen

I need to create an alphabetical list that looks like this

Brown, Larsen
Doe
Smith, Smith

In other words, the list should be ordered by name, but names in the same apt should be kept together.

Yesterday I got some help keeping names in the same apartement together(see below: simple sorting problem?) However,I still haven't managed to produce the list in alphabetical order! Any ideas???

saloon12yrd

Not tested but this might work:

SELECT * FROM table ORDER BY apt_no, name;
$apt = false;

while ([...fetch dataset into assoc. array $aptname...]) {
if ($apt != $aptname['apt_no']) {
$apt = $aptname['apt_no'];
// new apt starts here
echo "<BR>\n$apt";
}
echo " " . $aptname['name'];
}

Greets,

Dominique

claire

pls, could you let me know how to write this line:

while ([...fetch dataset into assoc. array $aptname...]) {

I tried

$sql = SELECT....

while ($aptname = mysql_fetch_array($sql)) {

The return is a very nice list but still ordered after apts not names!! However, it does keep names together for each apt.

claire

still haven't managed to get my list ordered alphabetically!

khaine

You could try something like

$query="select * from tablename order by name, group by apt_no";
$result=mysql_query($query);
if ( !$result )
{
echo nysql_errno()." : ".mysql_error()."<P>";
exit;
}

while ( $fields=mysql_fetch_array($result, MYSQL_ASSOC) )
{
echo $fields[apt_no]."-".$fields[name]."<BR>";
}

I think that should work.

Mark.

claire

I get the following error msg

1064 : You have an error in your SQL syntax near 'Resource id #2' at line 1

claire

Found the error...

The GROUP BY apt_no in the SELECT line simply groups two entries of the same apt-no so that only one shows up. In other words one name gets lost.

This is the search line I had from the very start...until I realized that I need php to do the sorting not MySQL.

I'm giving up thought, so many people have tried to help me and nothing seems to work!

Thanks anyway!