带有空格的字符串的MySQL不区分大小写的查询

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

所以我试图做一个相当简单的任务,比较一个字符串与mysql表行/条目使用例如。

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.

我的表是整理的:latin1_swedish_ci应该在不区分大小写的情况下进行搜索(就像我的其他查询一样),我相信因为字符串包含空格可能是问题,所以它只是查看第一个字符的大小写。

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.

我尝试在我的查询中使用'上'和'下'比较以及使用'LIKE'我也尝试使用REPLACE删除空格但我可能做错了,不完全确定。但它似乎永远不会找到正确的条目,除非我在数据库中输入它。

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.

我知道这个是完全错误的,并不是以这种方式使用REPLACE我假设,应该在UPDATE或INSERT中使用?无论如何,你可以通过删除所有内容的空白来看到我想要做的事情。

$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."')");

2 个解决方案

#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 . '%\')');

#2

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.

它返回结果但是在我得到结果后我在if语句中比较它们,例如/ $ string == $ db_string(我不需要,我不知道为什么我这样做)和它给了我'假',因为PHP考虑了区分大小写的数据。

Sorry guys!

#1