Jump to content

Searching by CASE in SELECT query.


hondaman

Recommended Posts

Hi guys, nice forum. I have been playing on and off with php for awhile now but have never encountered the problem I have now and I would like to see if anyone can help me.

I am currently getting a used vehicle feed sent to me by a provider which updates my database daily. Problem is, it is not indexed nor do the tables have what i consider "real relationships". Anywho...

The problem I have is that in one table... vehicles, currently gives me the options for each vehicle using THEIR coding system which oddly enough uses upper and lower case letters as the unique id for the options table.

ie. (Options Table)
ID DESC
----- ------------------
TA Power Windows
Ta Power Door Locks
RA Keyless Entry
Ra etc.....

My question is, how can I tell my query to only select the value that matches the correct case??? I am using the query below to no avail.

$thisOptionID = "Ta";
$fq = "SELECT DISTINCT id, desc FROM options WHERE id = '$thisOptionID'";
$fr = mysql_query($fq) or die("Error in Option Select Query: ".mysql_error());
$fi = mysql_fetch_array($fr);

What it gives me back is Power Windows when I want it to give me Power Door Locks.

Please help.
Link to comment
Share on other sites

Try this:
[code]SELECT DISTINCT id, desc FROM options WHERE id = '$thisOptionID' COLLATE latin1_general_cs[/code]

This will break the indexing, but if it works for you, you can set the `id` column to the latin1_general_cs collation permanently with ALTER TABLE. This would allow you to create a case sensitive index, and you could go back to using your original query.
Link to comment
Share on other sites

If you're using MySQL < 4.1, use the BINARY operator to make the case stick:

[code]SELECT DISTINCT id, desc FROM options WHERE id = BINARY '$thisOptionID'[/code]

But as wickning1 indicated, if this is how you want all your queries to work, you'll have to ALTER the table to set the column to this mode.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.