i have a problem with selecting some data from mys

问题:

I have both side coma separated values like

 $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

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

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

i already have condition as an array

   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)

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),')

回答1:

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

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

And a list of given ingredients:

1,2,3,4,5,6

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
)

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

The query:

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(*)
)

And the result:

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

回答2:

As far as I can see, the condition You specified matches only when rcring contains $ing exactly as substring, e.g. rcring = "5,2,1,6,2,3,45" wouldn't match $ing = "1,2,3,4,5,6" from your example. To match here, rcring should be stored as "1,2,3,4,5,45".

Simple sorting wouldn't help here, as $ing = "1,2,5,6" wouldn't match rcring = "1,2,3,4,5,6,45"

Simple way

Make $conditions[] contain a conjuntion of matches on distinct IDs, e.g.

<?
foreach (explode(",", $ing) as $key => $val) {
    $conditions[] = "CONCAT(',', `rcring`, ',') REGEXP ',($id),'"
}
?>

This will check what You want -- if rcing contains all IDs from $ing

However, this is quite slow for large (> 10^8 rows) tables. Such queries will work for O(n) time, checking all the rows in the table. For several gigabyte tables, it can take significantly more than a minute.

Optimization

Use Fulltext index searches. You need to store IDs separated by space, e.g. 1 2 3 4 5 45, to allow index treat it as words. After indexing, the code may be rewritten like following:

<?
    $plus_ing = "+1 +2 +3 +4 +5"
    $conditions[] = "MATCh(`rcring`) AGAINST($plus_ing)"
?>

Now that will work quite fast, and would skip the rows that don't match at all!

BE CAREFUL!

Always use PHP::PDO and pass IDs as parameters!

Hackers may abuse your system by making SQL injections with passing something dangerous instead of IDs, for example: 123");DROP DATABASE TEST; COMMIT;// This will turn $ings into smth like

"+1 +2 +123\");DROP DATABASE TEST; COMMIT;//"

and final query will look like

SELECT * FROM USERS WHERE MATCh(`rcring`) AGAINST(+1 +2 +123\");DROP DATABASE TEST; COMMIT;//");

The right way is to PDO prepared statements howto and to always construct queries that way!

回答3:

You need to split the $ing and check all its parts in the where-clause:

<?php
$ings = explode(',',$ing);
$conditions = array();
foreach($ings as $i) {
    $conditions[] = "CONCAT(',', `rcring`, ',') REGEXP ',(".intval($i)."),'";
}
$where = implode(' AND ', $conditions);
/* Use $where in where clause in your sql query */