Question

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?

<>>>>>

在对阵列(diff)的答复中收到一些很好的建议,尽管在我看来,非行的表确实是巨大的,阵列中的项目不超过5-6个。因此,我想最好回答5-6个问题。

Answer 1

如PHP阵列短,请设<代码>UNION ALL查询,以建立small table,然后使用NOT IN或LEFT JOIN。对large的查询(以更快的速度) 表:

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

或者,您可在临时表格中插入“ php”阵列值,并使用<条码>或<条码>>查询。当然,这意味着你最后提出三个额外的询问:

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

Answer 2

如何做到这一点?

<?php
    $a = array( a ,  b ,  c );
    $values = implode(" , ", $a);
    $sql = "SELECT DISTINCT `value` FROM `mytable` WHERE `value` IN ( $values )";
    echo $sql;

进行 s。结果将是你已经掌握的0至3个要素。接下来是一阵列(这根本不会是沉重的),因为你有最初的小阵列,而布邦的阵列则更少。

$not_in_db = array_diff($a, $array_from_sql_result);

如果你用 com子分离的价值观加以扼杀,那么你首先需要“开发”:

$s = "a,b,c";
$a = explode(",", $s);

Answer 3

您可以选择表格中的所有条目,然后填写array_diff(。

但这只是一个问题,是一个问题,有些是员额处理。

Answer 4

为此,我将把数值从表格中推入一个阵列,并使用array_diff。

REF:

Answer 5

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.

例如:

PHP阵列价值:a,b,c

亚洲开发银行的价值观:a,b,d

select value from your_table_name where (value LIKE  %a% ) OR (value LIKE  %b% ) OR (value LIKE  %c% );

页: 1

现在使用array_diff php。

Answer 6

select max(*) as count from tablename 
where field1 != $arr[0] && field2 != $arr[1] && field3 != $arr[2];

如果你愿意,你可以使用或操作。如果这一询问的回报为0,那么数据库中已不存在阵列价值。

友情链接