Page 1 of 1

Sorting alphabetically

Posted: Thu May 20, 2004 8:42 am
by jb
I am successfully returning results from my database using the following code, but I want the results to be listed alphabetically by surname.

I have tried to add ORDER BY surname to $query without success.

I am now stuck as I'm not sure if the SORT function should be used somewhere in the while loop.

Any help would be much appreciated......

$query = "SELECT name.surname, name.forename, name.extention

FROM name

WHERE name.departmentID = ".$dept ;

$result = mysql_query($query);


while ($record = mysql_fetch_assoc($result)) {

while (list ($fieldname, $fieldvalue)=each($record)){
echo "$fieldvalue ";
}
echo"<BR>";

Posted: Thu May 20, 2004 8:44 am
by delorian
This should work:

Code: Select all

$query="SELECT surname, forename, extenction FROM name WHERE departmentID = ".$dept." ORDER BY 1";

Posted: Thu May 20, 2004 9:00 am
by jb
Thanks very much.

It's interesting it sorts from 1 and not 0.

Do you have any suggestions as to the best approach for formatting the results?

I want to put a comma after the surname to make it more readable.
Because the whole result is being returned by $fieldvalue I'm not sure what approach I should be looking towards.

Thanks again.

Posted: Thu May 20, 2004 10:03 am
by delorian
There are as many good suggestions as you can possibly imagine.

If you want to pot comma after the surrname try this:

Code: Select all

while ($record = mysql_fetch_assoc($result)) { 
foreach($record as $fname => $fvalue) {
        echo $fvalue.($fname == 'surname' ? ", ":"");
} 
echo"<br />";
}

BTW: Read this ->viewtopic.php?t=21171

Posted: Thu May 20, 2004 10:52 am
by jb
Many thanks!