[MySQL] Sorting database results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

[MySQL] Sorting database results

Post 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....
User avatar
enygma
Site Admin
Posts: 175
Joined: Fri Apr 19, 2002 8:29 am
Location: Dallas, Tx

Post by enygma »

you could just use PHP to remove those characters and then sort them instead of using the order by....

-enygma
User avatar
cwcollins
Forum Commoner
Posts: 79
Joined: Thu May 16, 2002 3:51 pm
Location: Milwaukee, WI, USA

Re: [MySQL] Sorting database results

Post by cwcollins »

or, you could update all of the entries that use []'s (that is, remost the []'s).

-c.w.collins
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

Post 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 :wink:

Thanks for the help though
User avatar
cwcollins
Forum Commoner
Posts: 79
Joined: Thu May 16, 2002 3:51 pm
Location: Milwaukee, WI, USA

Post 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
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post 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
User avatar
cwcollins
Forum Commoner
Posts: 79
Joined: Thu May 16, 2002 3:51 pm
Location: Milwaukee, WI, USA

Post by cwcollins »

There's my way, and then there's the right way.
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

Post 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.... :)
User avatar
cwcollins
Forum Commoner
Posts: 79
Joined: Thu May 16, 2002 3:51 pm
Location: Milwaukee, WI, USA

Post 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
MattF
Forum Contributor
Posts: 225
Joined: Sun May 19, 2002 9:58 am
Location: Sussex, UK

Post by MattF »

Is there an HTML escape code for square brackets? If there is it may solve your problem ( I mean things like & ).
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

Post 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...
Spinball
Forum Newbie
Posts: 6
Joined: Thu Jun 13, 2002 12:23 pm

Post 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
Post Reply