[SOLVED] Sorting Street Addresses

[pkenny9999]

This was part of an earlier question, but I'll post it as stand alone.

 

When I do an ORDER BY query on my database, how do I get the street addresses to sort in order by street name rather than the street number that preceeds it? Currently they order by the street number.

 

 

[nikefido]

how's your table set up? do you have a separate column for the number, or is the number and address part of the address line?

 

My solution would be to either separate the number for the address in the database, or organize it by Town and/or State rather than address.

[revraz]

Or strip out the numbers.

[pkenny9999]

Currently the Street number and name are in the same field. I suppose I could seperate them, but it would mean recoding a ton of forms afterward. Just was hoping that there was something that I could do to have the query ignore any numbers and jump to the text. But I really need them be grouped by street in order to suit by needs.

[toplay]

This looks for the first space and grabs everything else after that and sorts by it:

 

SELECT

          SUBSTRING(`address_column`, LOCATE(' ', `address_column`) + 1) AS street_name

  FROM

          table_name

 

ORDER BY

          street_name ASC  # for descending use DESC

;

 

 

For instance this:

 

SELECT SUBSTRING('123 Some Street', LOCATE(' ', '123 Some Street') + 1)

 

would return:

 

Some Street

 

 

 

 

[revraz]

Nice, I was thinking to do it with a explode, but that looks clean.

[tinker]

well i've just done it this way:

$s = "SELECT * FROM test_addr ORDER BY SUBSTRING_INDEX(street, ' ', -1) ";

 

 

I've just realised I was moved with the forum... weird, started in Help, posted and was in MySQL... lol

[toplay]

well i've just done it this way:

$s = "SELECT * FROM test_addr ORDER BY SUBSTRING_INDEX(street, ' ', -1) ";

 

 

I've just realised I was moved with the forum... weird, started in Help, posted and was in MySQL... lol

 

tinker, that would not work when there's more than one space in the street address. For instance, this:

 

SELECT SUBSTRING_INDEX('123 Some Street', ' ', -1);

 

would just return (and sort on):

 

Street

[pkenny9999]

Okay, I get the concept, but unsure of the exact syntax.

 

This is what I used:

 

/*Querying the table*/
$query = mysql_query("select * SUBSTRING(`address`, LOCATE(' ', `address`) + 1) AS street_name from `Address` WHERE territory='$idx' ORDER BY street_name");	

 

But the above returns the following: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/territory_print.php on line 98

 

Abviously removing the * returned nothing.

 

What am I missing, I'm sure it's simple

[tinker]

First time i've ever used it, but your very correct, it's annoying though, not you but the fact that there's no * or something...

[pkenny9999]

Solved it myself, simple syntax error. Needed a , after select *.

 

The following works great

 

$query = mysql_query("select *, SUBSTRING(`address`, LOCATE(' ', `address`) + 1) AS street_name from `Address` WHERE territory='$idx' ORDER BY street_name ASC");

 

Thanks for all the help!

[toplay]

Your table name and column name are the same I see. I would change that to avoid confusion. The table could be called "addresses" while your column could remain "address". Just a suggestion.

 

I added the alias "a" and changed query to:

 

$query = mysql_query("select a.*, SUBSTRING(`address`, LOCATE(' ', `address`) + 1) AS street_name from `Address` a WHERE territory='$idx' ORDER BY street_name");

 

Please note that you should not execute the mysql_fetch_assoc() without first making sure the query worked.