在PHP中对逗号分隔值列表运行select

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:

我在数据库中运行select查询时遇到了一些问题。有些数据作为逗号分隔值的列表保存，例如:

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);
?>

The script above should return row 1 after finding a '1' in the People column but clearly I have mucked it up.

上面的脚本应该在人员列中找到“1”后返回第1行，但显然我把它弄脏了。

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.

我所遇到的问题是，我认为我的IN语句是反向的，也就是说，如果$selection是一个逗号分隔(内爆)列表，而不是我尝试使用它的方式，那么这个方法将是选择值。

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

我不知道它是否对我从上面的db类发送和接收的内容太重要了，但是如果您想知道，它只是一个简单的脚本，它运行所有相关的位并返回结果的数组。目前它没有返回任何东西，因为它没有找到匹配

Thanks in advance for anything you can tell me.

提前谢谢你能告诉我的一切。

Cheers -Dave

欢呼声戴夫

1 个解决方案

#1

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.",%'";

or

或

SELECT * 
  FROM $tbl_name 
 WHERE FIND_IN_SET(".$selection.",People);