Sort mysql varchar list alphabetically, numbers first

[martal]

Hello again

You were all a great help the last time when I needed assistance with some difficult CSS. Maybe you can help again with PHP/MySQL.

For my community website, I have a list of self catering properties.

Fields are name, proprietor, location, price etc.

I want to sort by name, alphabetically.

I use phpMyAdmin.

The field, 'name', is VARCHAR(36); it is unique and primary.

In PHP, I have:

$result = mysql_query("SELECT * FROM sc ORDER BY name");

phpMyAdmin browse, with a sort on name, sorts correctly:

30A ...
Ab...
Ach...
An A...
An C...
An S...
Cal
etc

However, on the webpage, I have:

Ach...
An A...
30A ...
Ab...
An C...
and then it's correct.

I have just added a number to the start of 'name' in my b and b table, which is almost identical. The page displays correctly, 3 Dr... at the start.

(I had an auto-incrementing ID field on sc, which I deleted as I was trying to fix this. Don't know if that field should go back when the sort is fixed.)

Is this problem some characteristic of varchar or something in phpMyAdmin that I need to address?

I have been trying various options with ORDER BY but none sucessful.

Looking forward to any replies.

Mart

[topr8]

not sure what the issue is, however you're also going to run into another issue sorting numbers in a varchar field,

consider the following numbers
1
4
200
1000

in an ordered varchar column they will be sorted in the following order:
1
1000
200
4

[rocknbil]

I haven't tried this, but it **should** work.

SELECT * FROM sc ORDER BY 0+name asc, name asc

If that puts the numbers last (which it might, see below) try

SELECT * FROM sc ORDER BY (0+name)>0 asc, (0+name)=0 asc

This forces the field to be evaluated as an integer. For ones without numbers this will probably evaluate as zero, which will put the non numeric ones first, so you might have to use the second select.

[martal]

This reply to topr8.

Yes, but I have only one numeric in the list. Any more, I will say 'please supply a name for your property, not an address!'

In my b and b table, I have edited some names.

1 Tig...
4 Cre...
200 An C...
1000 Mac

They are sorted as you predict.

But, in sc, with 30A Har... renamed to Har

I get:

Ach...
An A...
Ab...
.
.
Har (in the right position).

This is wrong without any leading numbers!

Am now going to delete these three, then rewrite.

Back shortly.

Missed rocknbil. Will try his suggestion.

[martal]

Ah well

rocknbil, I tried that (second) and got a strange sort.

rocknbil and topr8

I deleted the rows that were behaving strangely and rewrote them.

The sort is good now. No problems.

So, between myself and PhpMyAdmin, the table went wrong, maybe on the indexes. I was dodgy on that. Deleted and recreated a lot.

Feeling a bit embarrassed for wasting people's time. But, the talking about the problem can fix the problem!

phpMyAdmin was my first port of call. I'm not blaming it and am comfortable with it but is there a better alternative? What do the professionals use?

[Anyango]

Try MySQL WorkBench

[rocknbil]

What do you mean "strange?" It works. I had it backwards in my example, but here it is every way.

create table testme (id int(11) primary key auto_increment, someval varchar(30) not null);
insert into testme(someval) values ('012abc');
insert into testme(someval) values ('abc');
insert into testme(someval) values ('30def');
insert into testme(someval) values ('def');
insert into testme(someval) values ('12gkh');
insert into testme(someval) values ('rjk');

Sort with numbers first: 
select *,0+someval from testme order by (0+someval)=0 asc,(0+someval)>0 asc; 
+----+---------+-----------+ 
| id | someval | 0+someval | 
+----+---------+-----------+ 
| 1 | 012abc |  12 | 
| 3 | 30def |  30 | 
| 5 | 12gkh |  12 | 
| 2 | abc  |   0 | 
| 4 | def  |   0 | 
| 6 | rjk  |   0 | 
+----+---------+-----------+ 
Sort with the numbers last: 
select *,0+someval from testme order by (0+someval)>0 asc, (0+someval)=0 asc; 
+----+---------+-----------+ 
| id | someval | 0+someval | 
+----+---------+-----------+ 
| 2 | abc  |   0 | 
| 4 | def  |   0 | 
| 6 | rjk  |   0 | 
| 1 | 012abc |  12 | 
| 3 | 30def |  30 | 
| 5 | 12gkh |  12 | 
+----+---------+-----------+ 
Sort ignoring the numbers completely: 
select *,0+someval from testme order by ((0+someval)>0 or (0+someval)=0) asc; 
+----+---------+-----------+ 
| id | someval | 0+someval | 
+----+---------+-----------+ 
| 1 | 012abc |  12 | 
| 2 | abc  |   0 | 
| 3 | 30def |  30 | 
| 4 | def  |   0 | 
| 5 | 12gkh |  12 | 
| 6 | rjk  |   0 | 
+----+---------+-----------+ 

[topr8]

>>Try MySQL WorkBench

i ditto that! i use it for development, it comes free with mysql, you can download it from the mysql site