How can I list address number and street alphanumerically

[johnliverpool]

Hi all

 

Hope someone can help I would like to list my address's in both numeric and alphabetic order as below:

 

33 Connaught Rd, Kensington, L18 7HX

69 Connaught Rd, Kensington, L7 8RW

111 Connaught Rd, Kensington, L7 8RW

 

the script I use as follows:

 

$query="SELECT * FROM $useron ORDER BY `address1` ASC";
$result=mysql_query($query);

$num=mysql_num_rows($result);

if ($num > 0 ){

};
mysql_close();

if ($num > 0 ) {
$i=0;
while ($i < $num) {
$housenum = mysql_result($result,$i,"housenum");
$address1 = mysql_result($result,$i,"address1");
$address2 = mysql_result($result,$i,"address2");
$postcode = mysql_result($result,$i,"postcode");
$status = mysql_result($result,$i,"status");
$id = mysql_result($result,$i,"id");

?>
<table width="850" border="0" style="font-size:12px" >
  <tr>
    <th width="350" height="10" scope="col" align="left"><? echo  $housenum, " ", $address1, ", ",  $address2, ", ", $postcode; 
  ?></th>
    <th width="150"  height="10"scope="col" align="left"> Work Status: <font color="#FF0000"> 
      <?php	echo $status; ?></font>
   
<th width="350"  height="10"scope="col" align="left"><? 
echo " <a href=\"update.php?id=$id&useron=$useron\">Update</a> - 
<a href=\"delete.php?id=$id&useron=$useron\">Delete</a> - 
<a href=\"workrequest.php?id=$id&useron=$useron\">work request</a>";

if ($status=="Active"){
 echo " <a href=\"vwr.php?id=$id&useron=$useron\">View work requested</a>";
} ?>    </th> 
  </tr>
</table>

 

Lists the address's as below:

 

111 Connaught Rd, Kensington, L7 8RW Work Status: Idle    Update - Delete - work request

33 Connaught Rd, Kensington, L18 7HX Work Status: Idle    Update - Delete - work request

69 Connaught Rd, Kensington, L7 8RW Work Status: Idle    Update - Delete - work request

 

Any help would be much appreciated,

 

For any advice given thanks,

 

John

[AbraCadaver]

Try:

 

SELECT * FROM $useron ORDER BY address1+0 ASC

[dougjohnson]

Why not make "sort by" links at the top of each column - housenum, address1, address2, postcode.  Clicking on the "sort by" links would sort by the column data.  Or, I may have not understood your question?

[xyph]

Is the housenum column an integer type? or varchar?

 

If it's an integer type, use

 

SELECT `cols` FROM `table` ORDER BY `address1`, `housenum`

[johnliverpool]

Try:

 

SELECT * FROM $useron ORDER BY address1+0 ASC

 

This did'nt work thanks though

[johnliverpool]

Why not make "sort by" links at the top of each column - housenum, address1, address2, postcode.  Clicking on the "sort by" links would sort by the column data.  Or, I may have not understood your question?

 

I know what  your saying but would like house num and address alphanumerically listed from the start,

 

thanks for reply

[johnliverpool]

Is the housenum column an integer type? or varchar?

 

If it's an integer type, use

 

SELECT `cols` FROM `table` ORDER BY `address1`, `housenum`

 

They are set as varchar but can change if you can give me more info.

[johnliverpool]

Is the housenum column an integer type? or varchar?

 

If it's an integer type, use

 

SELECT `cols` FROM `table` ORDER BY `address1`, `housenum`

 

Thanks xyph,

 

"SELECT * FROM $useron ORDER BY address1, housenum ASC";

 

this worked