Change list from id to alpabetical

[johnliverpool]

Hi all

 

At present my PHP script  echo's the address's direct from the DB id, I would like to change this so it lists by alphanumeric:

 

address1 contains the number and street so at present lists as:

 

8 better street, city, postcode

1 better street, city, postcode

9 any street, city, postcode

1 any street, city, postcode

 

I would like:

 

1 any street, city, postcode

9 any street, city, postcode

1 better street, city, postcode

8 better street, city, postcode

 

Present script:

 

<?php   
     $useron = $_SESSION['user_on'];
      if (isset($_GET['msg'])) {
     echo "<div class=\"error\">$_GET[msg]</div>";
     }     
    

$query="SELECT * FROM $useron";
$result=mysql_query($query);

$num=mysql_num_rows($result);

   if ($num > 0 ){
      
   };
mysql_close();

if ($num > 0 ) {
$i=0;
while ($i < $num) {
$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 $address1, ", ",  $address2, ", ", $postcode; 
  ?></th>
    <th width="150"  height="10"scope="col" align="left"> Work Status: <font color="#FF0000"> 
      <?php   echo $status; ?></font>

 

this script may seem rubbish but it works

 

Any help much appreciated.

 

regards

 

johnliverpool

[MasterACE14]

$query="SELECT * FROM $useron ORDER BY `address1` ASC";

[johnliverpool]

$query="SELECT * FROM $useron ORDER BY `address1` ASC";

 

thanks for your quick reply

 

Inserted the query string you gave the list is no longer by id but still not alphanumeric

 

20, 121 Grant Av, Wavertree, L15 7GH         Work Status: Idle    Update - Delete - work request

19, 276 Smithdown Rd, Wavertree, L15 0RT Work Status: Idle    Update - Delete - work request

18, 29 Kenmare Rd, Wavertree, L15 3HG Work Status: Idle    Update - Delete - work request

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

24, 37 Ferndale Rd, Wavertree, L15 8FG Work Status: Idle    Update - Delete - work request

 

first number is id second house number and street, hope this helps.

 

 

[trq]

In order to efficiently sort alphabetically, you would need to store the street separately from the street number.

[johnliverpool]

In order to efficiently sort alphabetically, you would need to store the street separately from the street number.

 

Thanks for the response have done as requested;

New script:

 

  <?php	
  $useron = $_SESSION['user_on'];
      if (isset($_GET['msg'])) {
  echo "<div class=\"error\">$_GET[msg]</div>";
  }	  

$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 $id, ", ", $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>";
} ?>   

 

Hope you can work something with this Thorpe

 

Regards

 

John

[johnliverpool]

Noticed that by seperating the house number the address1 field is now list alphabetically:

 

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

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

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

34, 16 Grant Av, Wavertree, L15 0RT         Work Status: Idle    Update - Delete - work request

31, 8 Greenhill Close, Kensington, L18 7HX Work Status: Idle    Update - Delete - work request

35, 51 Kenmare Rd, Wavertree, L18 7HX Work Status: Idle    Update - Delete - work request

30, 21 Longfellow Street, Toxteth, L8 0RR Work Status: Idle    Update - Delete - work request

 

If I can also get the number numerically then that would be great

 

Thanks for the help

 

John

[johnliverpool]

Still hoping on a script that will list both house number and address alphanumerically,

 

Any replies will be really appreciated.

 

Regards

 

john

[sasa]

SELECT * FROM $useron ORDER BY `address1` ASC, CAST(`house_number` AS UNSIGNED) ASC

[xyph]

CASTing to sort is slow, but the idea is right. Make sure your house numbers are stored in an INT column and you can ignore that.