How can fetch data NOT IN another query array using PHP PDO-CodePudding

From the second query, I want to get users whose id are not in the first query. Please how do i run this? Below is what I have done so far.

 $followpeople = $conne->prepare('SELECT thisuser from followerstable where ids = :ids ');

$followpeople->execute(array("ids "=>$ids )); $followers = $followpeople->fetchAll(PDO::FETCH_ASSOC);

$users = $conne->prepare(" SELECT * FROM usersdb LEFT JOIN followerstable p ON r.ids = p.ids where r.ids NOT IN ('" . implode("', '", $followers["thisuser"])."') order by rand() LIMIT 3;");

CodePudding user response：

I think you don't need to do the first Query,

just directly query in usersdb table and left join it to the followerstable if the ids in followerstable is null it means it doesn't exist in followerstable

$users = $conne->prepare("SELECT * FROM usersdb r LEFT JOIN followerstable p ON r.ids = p.ids where p.ids IS NULL order by rand() LIMIT 3;");

CodePudding user response：

You can use a NOT IN (SELECT) construction, so you don't need two statements. I assume the IDs in usersdb and followerstable mean the same person.

$users = $conne->prepare("SELECT * FROM usersdb WHERE ids NOT IN (SELECT ids FROM followerstable where ids = :ids) ORDER BY rand() LIMIT 3");

Or if the matching IDs are ids in usersdb and thisuser in followerstable, then it would be:

$users = $conne->prepare("SELECT * FROM usersdb WHERE ids NOT IN (SELECT thisuser FROM followerstable where ids = :ids) ORDER BY rand() LIMIT 3");