我在从mysql数据库双方CSV中选择一些数据时遇到问题 [英] i have a problem with selecting some data from mysql database both side CSV

问题描述

我有像

 $ing=1,2,3,4,5,6

并且数据库有一个带有值的表

and the database has a table with values

 IDS    5,2,1,6,2,3,45 // in database

我知道这不是一个好习惯，但是我必须这样做才能在一个查询中获取值.我也有一个单独的表，其中IDS是分开的，并且对应于单独的用户，例如

I know this is not a good practice but I had to do this to get the values in one query. i also have a separate table where the IDS are separate and corresponds to separate users like

  user 1    IDS 2 
  user 3    IDS 65 etc 

到目前为止，我正在使用

As of now I am using

 $conditions[] = "CONCAT(',', `rcring`, ',') REGEXP ',($ing),'";

它给我很好的结果，但是给了我其中两个$ ing都存在的值，我只希望具有至少所有$ ing的表

It gives me good results but it gives me the values in which either of the $ing exists i want only the tables which has atleast all the $ing

请帮助我，我尝试了一下工作，却在任何地方都找不到合适的解决方案.谢谢.

Can you help me please I tried my work and I cant get a proper solution anywhere . thankx .

      //EDIT

我已经有条件作为数组

   if($ser !=''){
                   $conditions[] = "rc_ser='$ser'";
                }if($fridge == 1){
                    $ing="0|";
                    $ctch2fr='checked';
                    foreach($_SESSION['fridge'] as $item){
                    $ing=$ing.$item."|";}
                   $conditions[] = "CONCAT(',', `rcring`, ',') REGEXP ',($ing),'";
                }
                 if (count($conditions) > 0) {
                $sql= implode(' AND ', $conditions);
                }

像这样作为数组中的条件，我在查询中这样称呼它

Like this as an condition in array and i am calling it like this in query

select * ,(select count(rcid) from recipe where ($sql)) as total from recipe where ($sql)

我尝试了以下答案，但总给我0个结果 当我打印查询时，它显示我是这样

i tried the answers below bu it gives me 0 result always when i print the query it shows me like this

 select *,(select count(rcid) from recipe where (CONCAT(',', `rcring`, ',') REGEXP ',(0),') ) as total from recipe where (CONCAT(',', `rcring`, ',') REGEXP ',(0),')

推荐答案

您似乎有一个配方表，其中包含用逗号分隔的成分列表:

You seem to have a recipe table that contains comma separated list of ingredients:

5,2,1,6,2,3,45

以及给定成分的列表:

1,2,3,4,5,6

您想找到可以使用给定成分准备的食谱(食谱成分是给定成分的子集).您需要编写构建以下查询的PHP代码:

And you want to find recipes that could be prepared with the given ingredients (recipe ingredients is a subset of given ingredients). You need to write PHP code that builds the following query:

SELECT *
FROM recipe
WHERE (
    FIND_IN_SET('1', rcring) > 0 AND
    FIND_IN_SET('2', rcring) > 0 AND
    FIND_IN_SET('3', rcring) > 0 AND
    FIND_IN_SET('4', rcring) > 0 AND
    FIND_IN_SET('5', rcring) > 0 AND
    FIND_IN_SET('6', rcring) > 0
)

基于您的尝试的PHP代码的粗略概述(您必须将其转换为准备好的语句):

A rough outline of the PHP code based on your attempt (which you must convert to prepared statements):

$conditions = [];
foreach($fridge_ingredients as $ingredient) {
    $conditions[] = sprintf("FIND_IN_SET('%d', rcring) > 0", $ingredient);
}
$query = sprintf("SELECT *
FROM recipe
WHERE (%s)", implode(" AND ", $conditions));

---

话虽如此，正确的解决方案是规范化您的数据.这是结构的概述:

---

Having said that, the correct solution is to normalize your data. Here is an outline of the structure:

CREATE TABLE recipe (recipeid INT NOT NULL PRIMARY KEY, name VARCHAR(100));
CREATE TABLE ingredient (ingredientid INT NOT NULL PRIMARY KEY, name VARCHAR(100));
CREATE TABLE recipe_ingredient(recipeid INT NOT NULL,ingredientid INT NOT NULL, PRIMARY KEY(recipeid, ingredientid));

INSERT INTO recipe VALUES
(1, 'recipe 1'),
(2, 'recipe 2'),
(3, 'recipe 3'),
(4, 'recipe 4');
INSERT INTO ingredient VALUES
(1, 'ingredient 1'),
(2, 'ingredient 2'),
(3, 'ingredient 3'),
(4, 'ingredient 4');
INSERT INTO recipe_ingredient VALUES
(1, 1),
(2, 1), (2, 2),
(3, 1), (3, 2), (3, 3),
(4, 1), (4, 2), (4, 3), (4, 4);

查询:

SELECT *
FROM recipe
WHERE recipeid IN (
    SELECT recipeid
    FROM recipe_ingredient
    GROUP BY recipeid
    HAVING COUNT(CASE WHEN ingredientid IN (1, 2, 3) THEN 1 END) = COUNT(*)
)

结果:

recipeid  |  name
----------+----------
1         |  recipe 1
2         |  recipe 2
3         |  recipe 3

这篇关于我在从mysql数据库双方CSV中选择一些数据时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！