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
$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.