Case insensitive Mysql query grouped by case sensitive
Posted: Wed Sep 22, 2004 8:28 pm
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
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