Case insensitive Mysql query grouped by case sensitive

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
allyhazell
Forum Newbie
Posts: 9
Joined: Mon Sep 13, 2004 12:43 pm

Case insensitive Mysql query grouped by case sensitive

Post by allyhazell »

Hi everyone,

I have built a basic search engine for a client of mine, the system works but I don't think it is particularly efficient. The background is that it is a Mysql database of abbreviations. There are thousands of different entries, with abbreviations being in different 'cases'. So there may be CEG, CeG, ceG etc with different meanings.
When someone does a search for (for example) 'CEG' I need it to do a case insensitive search on the database and pull out all meanings for CEG, CeG, ceG etc. But when I output them to the page, I want to output them all in a 'case' order. So for example:

CEG:
meaning 1
meaning 2

CeG:
meaning 1
meaning 2

etc.

The entries are stored individually in the database. So there is a unique ID, the abbreviation, and the meaning:
4567, CEG, meaning

etc.

Currently I'm outputting the data into arrays with PHP and then using some PHP to go through one by one checking the previous one etc. But I don't think this is the best way of doing it.

Is there a way of outputting in the way I want in the Mysql query itself? Or does anyone have any other suggestions? I'm really stuck.

Thanks in advance for your help

Alastair Hazell
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

create your own sorting function and use [php_man]usort[/php_man] ... or store a procedure/function in mysql to sort it.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Code: Select all

$sql="SELECT abbrev,meaning FROM tablename WHERE upper(abbrev) LIKE '%".strtoupper($srch)."%' ORDER BY abbrev";
Using the above methodology as a basis you should get what you want (NB written for postgres database but assume something similar available for MySQL).
Last edited by CoderGoblin on Thu Sep 23, 2004 7:48 am, edited 1 time in total.
allyhazell
Forum Newbie
Posts: 9
Joined: Mon Sep 13, 2004 12:43 pm

I can see that would work, but..

Post by allyhazell »

Thanks for that piece of code. Just one more question, currently the abbreviations are stored in the database as varchars. Am I right in thinking that I would need to change them from varchars for the mysql query to work? Varchar's are case insensitive when searched for aren't they?

Thanks

Alastair
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post by AGISB »

You need to set the additional attribute 'binary' for the field otherwise it is always case insensitive.
allyhazell
Forum Newbie
Posts: 9
Joined: Mon Sep 13, 2004 12:43 pm

Post by allyhazell »

Ok, so if I go in and change it to a binary varchar in the table, will that work ok or will that muck up the data that is already in there?

Sorry if I sound thick.

Alastair
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post by AGISB »

It should do nothing but change the varchar field to a case sensitive varchar field.

But you should check the MySql manual to be sure about what exactly happens. Just make a backup of the database before you try it.
Post Reply