I'd like to know how I check whether one or more of the elements (numbers in this case) in a string, eg. '1,2,3,5' are in another ,eg. '3,4,5,6'
3 and 5 are common elements to each string in that example.
In this case it is to create a SQL query based on the string comparisons. One column value in a db contains one number string, and needs to be compared to another. I need results that match values of each string.
$results = $db->query("SELECT * FROM db
WHERE comparisonString IN (".$idsString.")
")->fetchAll (PDO::FETCH_ASSOC);
But its not quite working... it could be a lateral or syntactic answer.
MORE SPECIFICALLY, I am only getting a result when the FIRST element in the comaprisonString matches the other string elements.
Ideally the solution will look something like this:
$results = $db->query("SELECT * FROM db
WHERE ELEMENTS IN comparisonString IN (".$idsString.")
")->fetchAll (PDO::FETCH_ASSOC);
"ELEMENTS IN" is made up syntax, but that's the sort of thing I'm after
First of all it smells like a bad schema design. Don't store delimited strings of values in your database. Normalize your data by creating a many-to-many table. It will pay off big time enabling you to normally maintain and query your data.
In the meantime if you're using MySQL and assuming that your table looks something like
CREATE TABLE Table1
(
id int not null auto_increment primary key,
column_name varchar(128)
);
and let's say you have sample data
| ID | COLUMN_NAME | |----|-------------| | 1 | 3,4,5,6 | | 2 | 4,6,22 | | 3 | 7,5,11 | | 4 | 9,12,1,3 | | 5 | 8,32,16 |
and you want to select all rows where column_name contains one or more values from a list 1,2,3,5 you can do either
SELECT *
FROM table1
WHERE FIND_IN_SET(1, column_name) > 0
OR FIND_IN_SET(2, column_name) > 0
OR FIND_IN_SET(3, column_name) > 0
OR FIND_IN_SET(5, column_name) > 0
or
SELECT *
FROM table1 t JOIN
(
SELECT id
FROM table1 t JOIN
(
SELECT 1 value UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 5
) s
ON FIND_IN_SET(s.value, t.column_name) > 0
GROUP BY id
) q
ON t.id = q.id
Output (in both cases):
| ID | COLUMN_NAME | |----|-------------| | 1 | 3,4,5,6 | | 3 | 7,5,11 | | 4 | 9,12,1,3 |
Here is SQLFiddle demo