johnliverpool

August 18, 2011

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

August 18, 2011

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.

August 18, 2011

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

August 18, 2011

Try:

SELECT * FROM $useron ORDER BY address1+0 ASC

This did'nt work thanks though

August 17, 2011

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

August 14, 2011

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

Any replies will be really appreciated.

Regards

john

August 14, 2011

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

August 14, 2011

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 :thumb-up:

Regards

John

August 14, 2011

$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.

August 14, 2011

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

Posted August 14, 2011 · August 14, 2011

Hi all

Have built a nice database and forms for my web site mostly thanks to previous posts I have read and copied and pasted from :hail_freaks: .

So thanks to all the members that post there helpful replies. :thumb-up:

Thanks to all

Johnliverpool

Sign In

johnliverpool

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by johnliverpool

How can I list address number and street alphanumerically

How can I list address number and street alphanumerically

How can I list address number and street alphanumerically

How can I list address number and street alphanumerically

How can I list address number and street alphanumerically

Change list from id to alpabetical

Change list from id to alpabetical

Change list from id to alpabetical

Change list from id to alpabetical

Change list from id to alpabetical

Hello and already thanks to lots of ur members

Browse

Activity

Important Information