Page 1 of 1

Case insensitive Mysql query grouped by case sensitive

Posted: Wed Sep 22, 2004 8:28 pm
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

Posted: Thu Sep 23, 2004 12:10 am
by feyd
create your own sorting function and use [php_man]usort[/php_man] ... or store a procedure/function in mysql to sort it.

Posted: Thu Sep 23, 2004 2:52 am
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).

I can see that would work, but..

Posted: Thu Sep 23, 2004 6:00 am
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

Posted: Thu Sep 23, 2004 7:37 am
by AGISB
You need to set the additional attribute 'binary' for the field otherwise it is always case insensitive.

Posted: Thu Sep 23, 2004 8:11 am
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

Posted: Thu Sep 23, 2004 10:19 am
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.