MySQL case insensitive query with strings with whitespace

Question

So I am trying to do a fairly simple task, compare a string with a mysql table row/entry using eg.

SELECT * FROM table_name WHERE $string = table_column.

My issue is, is that it is searching and remaining case sensitive.

The string: "A Long String"
The database entry "A Long String"

My table is collated: latin1_swedish_ci which should be searching in case insensitive (like the rest of my queries), I believe since the strings contain whitespace it could be the issue, it is only looking at the first character's capitalisation.

I have tried using 'Upper' and 'lower' comparisons in my queries as well as using 'LIKE' I have also tried removing whitespace using REPLACE but I am probably doing it wrong, not entirely sure. But it never seems to find the correct entry unless I type it as it is in the database.

Any advice would be cool!

$result = mysqli_query($db, "SELECT * FROM character_information WHERE 
(reference LIKE '".$origin_name."' AND char_name LIKE '".$char_name."') 
OR
(lower(reference) LIKE '".$origin_name."' AND lower(char_name) LIKE '".$char_name."') 
OR
(upper(reference) LIKE '".$origin_name."' AND upper(char_name) LIKE '".$char_name."') 
AND 
(char_gender = '".$char_gender."')");

Another try?

$result = mysqli_query($db, "SELECT * FROM character_information WHERE 
reference = '".$origin_name."' AND char_name = '".$char_name."' AND char_gender = '".$char_gender."'");

I know this one is completely wrong, not meant to use REPLACE in this kind of way im assuming, should be used in UPDATE or INSERT? Anyway you can see what I am trying to do by removing the whitespace from everything.

$origin_name = str_replace(' ', '', $origin_name);
$char_name = str_replace(' ', '', $char_name);

$result = mysqli_query($db, "SELECT * FROM character_information WHERE 
( (REPLACE(reference, ' ', '' )) LIKE '".$origin_name."' AND (REPLACE(char_name, ' ', '' )) LIKE '".$char_name."%') 
AND 
(char_gender = '".$char_gender."')");

Please give the exact code (for example: what is "$string"?). Are you sure that the strings are equal? In some languages characters exist that look similar when displayed but are not (when using Unicode). — Werner Henze
– Werner Henze, Commented May 8, 2015 at 12:43
I think query must be in this manner SELECT * FROM table_name WHERE table_column = $string . — Sunil Pachlangia
– Sunil Pachlangia, Commented May 8, 2015 at 12:44
I used that as an example, I know how to write a mysql query in PHP (i have quotes in it), I already tried using lower and upper. I will post my code. — Lizzeiy
– Lizzeiy, Commented May 8, 2015 at 12:56
If you statically pass it a value does it work? Something like SELECT * FROM character_information WHERE char_gender = 'male' — chris85
– chris85, Commented May 8, 2015 at 13:00

toomanyredirects · Accepted Answer · 2015-05-08 13:11:38Z

1

Upper and lower comparisons aren't needed at the same time. If you convert both the column in your database and the search term to one or the other as part of the same query, you'll be ensuring case insensitivity.

mysqli_query($db, 'SELECT * FROM table_name WHERE LOWER(table_column) = LOWER(\'' . $value . '\')');

Or for checking if a column contains the value rather than being equal to it:

mysqli_query($db, 'SELECT * FROM table_name WHERE LOWER(table_column) LIKE LOWER(\'%' . $value . '%\')');

answered May 8, 2015 at 13:11

toomanyredirects

2,00216 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lizzeiy Over a year ago

Thanks for the tip! I will use this if I need it in the future :)

Lizzeiy · Accepted Answer · 2015-05-08 13:19:12Z

0

I just realised what I was doing wrong,

It WAS returning a result but after I got the result I was comparing both of them in an if statement, eg/ $string == $db_string (which I don't need to, I don't know why I did this) and it was giving me 'false' since PHP takes into account case sensitive data.

Sorry guys!

answered May 8, 2015 at 13:19

Lizzeiy

4261 gold badge8 silver badges23 bronze badges

Collectives™ on Stack Overflow

MySQL case insensitive query with strings with whitespace

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related