如何使用mysql数据库数据删除php上的数组上的项目

Question

I want to do something like this

---

----sql output----
name TEXT
b
c

---

$arr = ["a","b","c","d"];

foreach($arr as $key=>$val){
    $query = "select count(*) from mydata where name = $val";
    $count = xxxx($query);
    if($count)unset($arr[$key]);
}

And $arr will be
a,d

Any simple way to do it but not checking every single element?

---

---edited---
I think I solve my problem myself...getting some inspired from mrlore that using having.

$arr = ["a","b","c","d"]; /* make the string inside arr safe for query */
$val = array_map(function($val){return "'$val'";},$arr);
$q = "select name from mydata where name in (" . implode(',',$val) .")";
$all = xxxxx($q);
$arr = array_diff($arr,$all);

This took me 0.8 second. Using my old way take 46 second and RST way takes 2 seconds.

---

---edited----
I have over 200k rows of data in my database, so I want a better way to optimize it.

---

--re MrLore--
ps : I don't know how to use stackoverflow to posting comments..so I just edit my post.

I have tried it on my mysql workbench but it does not seem to work for me.

select name from mydata  
where name in("a","b","c","d")  
having count(name) = 0  

-- returns nothing

select name from mydata  
where name in("a","b","c","d")  
having count(name) > 0  

Answer 1

$arr = ["a","b","c","d"];

    $query = "select DISTINCT name from mydata";
    $results = <result of query>;

    foreach ($results as $result){
      if(($key = array_search($result, $arr)) !== false) {
        unset($arr[$key]);
      }
    }

Or the other way around

    foreach ($arr as $key=>$value){
      if( in_array( $value, $results) ) {
        unset($arr[$key]);
      }
    }

By querying DISTINCT the database only returns unique values. You can then compare them with the array you have and remove them.