Let's say I have three values in PHP: "a", "b", "c". Doesn't matter whether in an array or comma separated string.
There is a table in database:
id | value
1 | a
2 | b
3 | d
My purpose is to find the values that are in php array but not in database table.
The given example will give "c".
Can I do it with only one query?
UPDATE
Received several good suggestions in answers about array_diff(), though in my case the DB table is really large and the array has not more than 5-6 items. So it would be better to perform 5-6 queries, I think.
If the PHP array is short, you can build a UNION ALL query to build your small table, then use NOT IN or LEFT JOIN query (whichever is faster) against the large table:
SELECT value
FROM (
SELECT 'a' AS value
UNION ALL
SELECT 'b'
UNION ALL
SELECT 'c'
) AS php_array_values
WHERE value NOT IN (
SELECT value
FROM that_large_table
);
Alternately, you can insert the php array values in a temporary table and use the IN or JOIN queries. Of course, this means you end up writing three extra queries:
CREATE TEMPORARY TABLE IF NOT EXISTS php_array_values (value VARCHAR(100));
DELETE FROM php_array_values;
INSERT INTO php_array_values VALUES ('a'), ('b'), ('c');
SELECT php_array_values.value
FROM php_array_values
LEFT JOIN that_large_table ON php_array_values.value = that_large_table.value
WHERE that_large_table.value IS NULL
You could select all the entries in the table and then perform an array_diff().
But this isn't one query, is one query and some post processing.
For this I would pull values from the table into an array and use array_diff
select max(*) as count from tablename
where field1 != $arr[0] && field2 != $arr[1] && field3 != $arr[2];
You can use and or or operators if you want. if the return count for this query is 0 then the array values are not exist in the database already.
Instead of pulling all the elements from the DB you can try using 'LIKE' statement, this will reduce the number of entries pulled from DB.
something like this :
PHP array values : a,b,c
DB values : a,b,d
select value from your_table_name where (value LIKE '%a%') OR (value LIKE '%b%') OR (value LIKE '%c%');
o/p of this will be : {a,b}
now use array_diff of php.
how about this?
<?php
$a = array('a', 'b', 'c');
$values = implode("','", $a);
$sql = "SELECT DISTINCT `value` FROM `mytable` WHERE `value` IN ('$values')";
echo $sql;
perform the sql query. the result will be those 0 to 3 elements you already have. next, do an array_diff (which will not be heavy at all, since you'll have your initial small array, and the array of those in the db, which is even smaller).
$not_in_db = array_diff($a, $array_from_sql_result);
if what you have is a string with comma separated values, then you'll need to "explode" it first:
$s = "a,b,c";
$a = explode(",", $s);