-1

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. The final goal is to get IDs in this format in the first query Example: "1", "2","3" etc and this IDs should not be part of the second second query.

 $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;");
Austin
  • 37
  • 7
  • You need to explain the **final goal**, the data you need. Because right now neither query makes any sense, so we cannot tell what you actually want – Your Common Sense May 15 '22 at 05:54

2 Answers2

0

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;");
VLDCNDN
  • 692
  • 1
  • 7
  • 19
  • Maybe you didn't get my questions. First I want to get all the IDs a user is following in the first query. Then after that, those IDs should not be part of the second query. – Austin May 15 '22 at 01:41
  • I expect your first query is already correct, right? In the second query you wanted to fetch the users that are not in `followerstable` base on the result of your first query? – VLDCNDN May 15 '22 at 02:06
  • Why are you guys preparing a statement with no parameters in it? – Aranxo May 15 '22 at 02:59
  • Yes, users that are not in the followerstsble only but based on a user not for all users. To clarify, if a user has followed a group of users before, he should not see them again but other users can as well see them since they have not follow before – Austin May 15 '22 at 03:30
  • For example, In the first query, the variable $followers will contain arrays of IDs in this format "1", "2", "3" and these IDs should not be part of the second query – Austin May 15 '22 at 05:39
  • @Austin for starter, your first query never works as intended. And as soon as you manage it to get working, I don't see how it's a problem go get the second one. – Your Common Sense May 15 '22 at 05:57
0

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");
Aranxo
  • 677
  • 4
  • 15
  • Why are you guys are preparing a statement with a single placeholder when apparently it requires many values to be bound? – Your Common Sense May 15 '22 at 05:18
  • @YourCommonSense I didn't care for the WHERE clause, so I just repeated what the OP did. And he used an one element array. But I agree to you, this doesn't make very much sense. I suggest, an another IN clause in the inner SELECT is needed. It's not that clear what the OP really wants to do. For me, the task was: Can we put this one together in one SQL statement (to maybe avoid a loop). – Aranxo May 15 '22 at 11:14