在PHP中的逗号分隔值列表上运行select [英] Running a select on a list of comma separated values in PHP
问题描述
我在数据库上运行选择查询时遇到一些问题。一些数据保存为逗号分隔值的列表,例如:
I am having a bit of a problem running a select query on a database. Some of the data is held as a list of comma separated values, an example:
Table: example_tbl
| Id | People | Children |
| 1 | 1,2,3 | 8,10,3 |
| 2 | 7,6,12 | 18,19,2 |
我想要运行的一个例子:
And an example of the kind of thing I am trying to run:
<?php
include 'class.php';
$selection = 1;
$db = new DbClass;
$tbl_name = 'example_tbl';
$sql = "SELECT * FROM $tbl_name WHERE ".$selection." IN People";
$result = $db->query($sql);
$print_r($result);
?>
在找到'1'后,上的脚本应该返回第1行
The script above should return row 1 after finding a '1' in the People column but clearly I have mucked it up.
我遇到的问题是我认为我有IN语句向后,即我想这个方法将是如果$ selection是一个逗号分隔(imploded)列表,而不是我想使用它的方式选择值。
The issue I am having is that I think I have the IN statement backwards, i.e. I THINK this method would be to select values if $selection was a comma separated (imploded) list, rather than the way round I am trying to use it.
我不知道对于我从上面的db类发送和接收什么太重要,但是如果你想知道,它只是一个简单的脚本运行所有相关位并返回结果数组。现在它不返回任何东西,因为它没有找到匹配
I don't know if it's too important about what I am sending and receiving from the db class above, but if you wanted to know, it's just a simple script that runs all relevant bits and returns an array of the result. at the moment it's returning nothing because it's not finding a match
感谢提前任何你可以告诉我。
Thanks in advance for anything you can tell me.
干杯
-Dave
Cheers -Dave
推荐答案
坚持在列中使用逗号分隔数据值的疯狂习惯,而不是正确地标准化他们的数据库设计。 总是 会导致问题。
Why do people insist on this insane habit of comma-separated data values in a column rather than properly normalising their database design. It always leads to problems.
SELECT *
FROM $tbl_name
WHERE CONCAT(',',People,',') LIKE '%,".$selection.",%'";
或
SELECT *
FROM $tbl_name
WHERE FIND_IN_SET(".$selection.",People);
这篇关于在PHP中的逗号分隔值列表上运行select的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!