PHP、MySQL:mysql 替代 php in_array 函数 [英] PHP, MySQL: mysql substitute for php in_array function
问题描述
假设我有一个数组并且我想检查一个元素是否是该数组的一部分，我可以继续使用 in_array(needle, haystack) 来确定结果.为了我的目的，我正在尝试查看与此等效的 PHP.现在您可能会立即为我解答，您可能会想说使用 IN".是的，我可以使用 IN，但这并没有获得所需的结果.让我用一个例子来解释:
Say if I have an array and I want to check if an element is a part of that array, I can go ahead and use in_array( needle, haystack ) to determine the results. I am trying to see the PHP equivalent of this for my purpose. Now you might have an instant answer for me and you might be tempted to say "Use IN". Yes, I can use IN, but that's not fetching the desired results. Let me explain with an example:
我在数据库表中有一个名为pets"的列.作为记录，它有一个值:Cat, dog, Camel(是的，列数据是逗号分隔值).考虑到这一行的 id 为 1.
I have a column called "pets" in DB table. For a record, it has a value: Cat, dog, Camel (Yes, the column data is a comma separated value). Consider that this row has an id of 1.
现在我有一个表单，我可以在表单输入中输入值并使用该值检查数据库中的值.所以说我在表单输入中输入以下逗号分隔值:CAT,camel(是的，CAT 是大写的，因为有些用户倾向于这样输入).
Now I have a form where I can enter the value in the form input and use that value check against the value in the DB. So say I enter the following comma separated value in the form input: CAT, camel (yes, CAT is uppercase & intentional as some users tend to enter it that way).
现在，当我在表单输入中输入上述信息并提交时，我可以收集 POST 的信息并使用以下查询:
Now when I enter the above info in the form input and submit, I can collect the POST'ed info and use the following query:
$search = $_POST['pets'];
$sql = "SELECT id FROM table WHERE pets IN ('$search') ";
上面的查询没有为我获取数据库中已经存在的行(还记得将 Cat、dog、Camel 作为 pets 列的值的记录吗?).我试图让记录充当超集，并将表单输入中的值作为子集.所以在这种情况下，我希望 id 值显示为列中存在的值，但这并没有发生.
The above query is not fetching me the row that already exists in the DB (remember the record which has Cat, dog, Camel as the value for the pets column?). I am trying to get the records to act as a superset and the values from the form input as subsets. So in this case I am expecting the id value to show up as the values exist in the column, but this is not happending.
现在假设我只输入 CAT 作为表单输入并执行搜索，它应该显示 ID 1 行.
Now say if I enter just CAT as the form input and perform the search, it should show me the ID 1 row.
现在假设我只输入 camel, cAT 作为表单输入并执行搜索，它应该显示 ID 1 行.
Now say if I enter just camel, cAT as the form input and perform the search, it should show me the ID 1 row.
我怎样才能实现上述目标?
How can I achieve the above?
谢谢.
推荐答案
您要找的函数是 find_in_set:
select * from ... where find_in_set($word, pets)
对于多词查询，您需要测试每个词和 AND(或 OR)测试:
for multi-word queries you'll need to test each word and AND (or OR) the tests:
where find_in_set($word1, pets) AND find_in_set($word2, pets) etc
这篇关于PHP、MySQL:mysql 替代 php in_array 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
