[SOLVED] run a MySQL select quary with insensitive case.

[shlomikalfa]

hey there i've made a db to storge many posts however i can't seem to find out how to make that database's search case-insensitive, please help ...

 

THANKS IN ADVANCE!

[cooldude832]

try the Like operator

<?php
$q = "select fields from `table` where fieldname like '".$value."'";

[GingerRobot]

Mysql is ,by default, case insenstive. A standard comparison query (using the = sign) is case insenstive. You can make searches case senstive, however.

 

See: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

[shlomikalfa]

SELECT HIGH_PRIORITY *
FROM downloads
WHERE `downloads`.`Title` = "My first dl"

--============

SELECT HIGH_PRIORITY * FROM downloads WHERE `downloads`.`Title` 
LIKE "My first dl"

 

i need the Title to be case insensitive... in the ways above it is case-sensitive!!!

anyone can help ?

 

I'd like to add 1 more EZ question that might help me alot!!!

- how do i get the latest indexed item from a certain table ?!

(Currently i'm using a redicules method which is select... where 1, and the feching the row numbers.)

 

THANKS IN ADVANCE !!!

[GingerRobot]

As the link i posted shows, you need to set the collation to one of the case senstive options. If you want all of your searches on that field to be case senstive, then modify the collation on the field.

 

If you just want this particular query to be case sensitive, then try:

 

SELECT HIGH_PRIORITY * FROM downloads WHERE `downloads`.`Title` COLLATE latin1_general_cs = "My first dl"

[shlomikalfa]

@GingerRobot

dear mr.dude, i don't want case-sensitive !!!

- i want case-IN-sensitive... how do i do that ?

 

p.s - i've read the page you've gave me many times already - still no clue why do i need to read it as i don't want my searches case-sensitive BUT case-IN-sensitive...

 

please help me sir.

[shlomikalfa]

why can't i edit my own post... bah - it's not a spam and sorry for the double post!

 

SELECT HIGH_PRIORITY * FROM downloads WHERE `downloads`.`Title` COLLATE utf8_bin = "My first dl"

-======

SELECT HIGH_PRIORITY *
FROM downloads
WHERE `downloads`.`Title` = "My first dl"

-======

SELECT HIGH_PRIORITY * FROM downloads WHERE `downloads`.`Title` 
LIKE "My first dl"

 

All of these give me no return! - 0 rows. {used directly on the mysql interface.}

but all of the following do give me the requested entry!!!

-. notice the M-m changes.

 

SELECT HIGH_PRIORITY * FROM downloads WHERE `downloads`.`Title` COLLATE utf8_bin = "my first dl"

-======

SELECT HIGH_PRIORITY *
FROM downloads
WHERE `downloads`.`Title` = "my first dl"

-======

SELECT HIGH_PRIORITY * FROM downloads WHERE `downloads`.`Title` 
LIKE "my first dl"

 

any idea anyone ?!

already over 8 hours trying to figure it out....

[GingerRobot]

Hmm, well looks like i misread. I can only assume that the field title has a case sensitive collation.

 

Try this query:

 

SELECT HIGH_PRIORITY * FROM downloads WHERE `downloads`.`Title` COLLATE utf8_general_ci = "my first dl"

 

If that works, then modify the collation on the field.

[shlomikalfa]

THANKS !!! 

 

i'd never realize it on my own !!!