Page 1 of 1
[MySQL] Sorting database results
Posted: Mon May 20, 2002 8:18 am
by ILoveJackDaniels
I have written a small search script in PHP, to return results from a MySQL database, which are listed alphabetically by the database automatically using the "ORDER BY" thingummy.
The problem is that this has been written for a client of mine. He has started entering data into the database and is using square brackets ( [ ] ) in places at the start of the field which is used to sort the results of the search, so although almost everything is ordered alphabetically, the fields using square brackets at the start are placed at the very end of any search.
Is it possible when grabbing results from a MySQL database to have the database ignore certain characters for the purposes of the ordering of the results? i.e. the results would be ordered alphabetically whether or not the field started with a square bracket....
Posted: Mon May 20, 2002 9:18 am
by enygma
you could just use PHP to remove those characters and then sort them instead of using the order by....
-enygma
Re: [MySQL] Sorting database results
Posted: Mon May 20, 2002 9:25 am
by cwcollins
or, you could update all of the entries that use []'s (that is, remost the []'s).
-c.w.collins
Posted: Mon May 20, 2002 9:32 am
by ILoveJackDaniels
Damn, it's looking less and less likely I'm going to be able to avoid doing any work to fix this problem. I hate my clients
Thanks for the help though
Posted: Mon May 20, 2002 9:45 am
by cwcollins
probably the easiest wat to make this update, if you are not a whiz with SQL, would be to bring it into Access, or some other DB application (possible via ODBC) and do a find-replace of "[" with "" and "]" with "". shouldn't take more than a few minutes.
good luck.
-c.w.collins
Posted: Mon May 20, 2002 10:04 am
by jason
Okay, this might take a little work, but you can try something like this:
Code: Select all
SELECT SUBSTR(bracketed_field,1,LENGTH(bracketed_field)) as good_field FROM table ORDER BY good_field
Posted: Mon May 20, 2002 10:36 am
by cwcollins
There's my way, and then there's the right way.
Posted: Mon May 20, 2002 10:43 am
by ILoveJackDaniels
Problem with replacing the square brackets is that the client actually wants them there...
Could do something along those lines, Jason. Maybe I could run a script on the thing to duplicate the field for the purposes of searching but not display, removing the square brackets and change the data entry script to add the duplicate field in as well. All sounds like much fun .... no really....

Posted: Mon May 20, 2002 11:02 am
by cwcollins
Jason's approach is definately the better way to go. why keep duplicate data if you don't have to?
-c.w.collins
Posted: Mon May 20, 2002 3:27 pm
by MattF
Is there an HTML escape code for square brackets? If there is it may solve your problem ( I mean things like & ).
Posted: Mon May 20, 2002 5:31 pm
by ILoveJackDaniels
Matt, yes there is, but that unfortunately would not solve the problem.
cw - you're right. I'll try some more fixes tomorrow...
Posted: Thu Jun 13, 2002 12:23 pm
by Spinball
I hope this might be a slightly better version for your answer :
Code: Select all
SELECT yourfield, substring(yourfield from 2) as dummy FROM yourtable order by dummy