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
Case insensitive Mysql query grouped by case sensitive
Moderator: General Moderators
-
allyhazell
- Forum Newbie
- Posts: 9
- Joined: Mon Sep 13, 2004 12:43 pm
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
Code: Select all
$sql="SELECT abbrev,meaning FROM tablename WHERE upper(abbrev) LIKE '%".strtoupper($srch)."%' ORDER BY abbrev";
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..
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
Thanks
Alastair
-
allyhazell
- Forum Newbie
- Posts: 9
- Joined: Mon Sep 13, 2004 12:43 pm