Sorting by street name and ignore number

Anon

I know how to sort output from a SELECT.

However in this case it won't help. There is a street address which includes a leading number, but I want to sort by the names of the streets alphabetically. I want a sort that could ignore the leading digits if that was possible.

It looks like I'd have to split apart the one field into two fields into a temporary table and then do a query on that to do what I want.

Any ideas?

Anon

I forgot to say, this is with MySQL, and of course PHP.

van__

Hmmm....
This isn't a fun one to be sure.

But since you want this feature, I'll give it to you.

There is not need for a temporary table, as that would only add an unnecessary step. You'll want to use a regex to create an array to use as keys to sort a hash table with....

$stmt = "SELECT * FROM myAddressBook";
$result = ...exec stmt...

for ($i=0;$i<mysql_num_rows($result);$i++) {
$rows[$i] = mysql_fetch_array($result, MYSQL_ASSOC);
preg_match('/[0-9]\s+(.)/s',$rows[$i]['address'],$streetname);

$sortBy[$streetname[1]] = $rows[$i];
}

ksort($sortBy);
print_r($sortBy);

and that should do it.
Atleast in theory, I haven't tested it, just off the top of my head.

Anon

That was an excellent piece of coding, Van. It worked well. Aside from adjusting for my vars and making it print pretty, the only change I needed was to sort it in a case insensitive way:

uksort($sortBy,strnatcasecmp);

in case the street name was entered starting with lower case.

paulsbooker

you may want to address the issue of normalizing your table further and putting distinct information (which may be searched on) into their own fields

paul

D Teed wrote:

That was an excellent piece of coding, Van. It worked well. Aside from adjusting for my vars and making it print pretty, the only change I needed was to sort it in a case insensitive way:

uksort($sortBy,strnatcasecmp);

in case the street name was entered starting with lower case.