Troubling ORDERS

[swatisonee]

I have this code below but the output on screen doesnt get sorted alphabetically. Any pointers please ? Many thanks !

 

  $resultxx = mysql_query(" SELECT DISTINCT CITYID FROM Address ORDER BY  `CITYID` asc   ");
  //ORDER here is immaterial

  if ($myrowxx = mysql_fetch_array($resultxx)) {

    do {
$cityid = $myrowxx["CITYID"];
$sqlcity1 =" SELECT  * FROM Cities WHERE CITYID = $cityid  ORDER BY  `City` asc ";
//ORDER here is irrelevant 

$resultcity1 = mysql_query( $sqlcity1 ) or die (mysql_error());
$myrowcity1 = mysql_fetch_array($resultcity1);
     
printf("<option value=%d>
        %s", $myrowcity1["CITYID"], $myrowcity1["City"]);

    } while ($myrowxx = mysql_fetch_array($resultxx));

  	echo "</select>\n";

  } else {

  	echo "Sorry, no records were found!";
  	echo "</select>\n";
  }

 

[joel24]

they're not alphabetical because you're ordering by a numeric value; CITY ID, order by city rather than city id.

[swatisonee]

Exactly and therein lies my problem. Because the 1st table has only the IDs it can sort numerically which is nullified when the city names are output thru the 2nd selection . So i wish to know how i can get the city selection sorted alphabetically.

 

they're not alphabetical because you're ordering by a numeric value; CITY ID, order by city rather than city id.

[jcbones]

I would suggest writing one query to do the work you have two doing.  It eliminates the headaches.

Un-tested:

SELECT a.CITYID, b.City FROM Address AS a JOIN Cities AS b ON a.CITYID = b.CITYID GROUP BY a.CITYID ORDER BY b.City ASC

 

It can be tricky (sometimes) getting a group by and an order by running on the same query.  So let us know if that doesn't work, we may have to go with a sub-query to get it working correctly.

[swatisonee]

It works perfectly !! Thank you so much. I ought to have thought about using JOIN.

 

I would suggest writing one query to do the work you have two doing.  It eliminates the headaches.

Un-tested:

SELECT a.CITYID, b.City FROM Address AS a JOIN Cities AS b ON a.CITYID = b.CITYID GROUP BY a.CITYID ORDER BY b.City ASC

 

It can be tricky (sometimes) getting a group by and an order by running on the same query.  So let us know if that doesn't work, we may have to go with a sub-query to get it working correctly.