Fetch questions that the logged in user hasn't answered yet using PHP and MYSQL

Question

I want to fetch and display a limited amount of questions that hasn't been answered yet by the logged in user only.

I already have this block of code, but I can't seem to find out how to select it distinctly for a certain logged in user.

SELECT
   a.* 
FROM
   questions as a 
   LEFT JOIN
      questions_answers as b 
      ON (a.q_id = b.q_id) 
WHERE
   b.q_id IS NULL 
   AND b.user_id = 2 
ORDER BY
   RAND() LIMIT 10

assuming "2" is a sample user ID of the logged in user.

The "AND b.user_id = 2" is where I'm stuck.

I'll appreciate any help.

UPDATE: Here's the table structure

Questions Table
q_id
question

Questions_Answers Table
ans_id
q_id
user_id

UPDATE: I have already answered this question and added it below. Thank you to everyone who suggested. I'll take your advice seriously as well. I'll appreciate it if you will upvote my answer if you have tried it and get what I meant. :)

SELECT DISTINCT a.* FROM questions as a LEFT JOIN questions_answers as b ON (a.q_id = b.q_id) WHERE b.q_id IS NULL AND b.user_id = 2 ORDER BY RAND() LIMIT 10 — Kmg Kumar, Nov 29 '18 at 06:28
Use a boolen flag column in table.update this column when user answers a question. retireve question by checking flag is true. — Sumesh TG, Nov 29 '18 at 06:28
@kmgkumar not working. The code must show the remaining questions that the logged in user needs to fill up — jaegyo23, Nov 29 '18 at 06:31
What's the absolute maximum number of questions that will be stored? ORDER BY RAND() can end up having quite a performance penalty with larger tables — SpacePhoenix, Nov 29 '18 at 07:06
@SpacePhoenix it's gonna be around 15-20 max questions. It's not a lot but the users who will answer are. — jaegyo23, Nov 29 '18 at 07:15

Muhammad Tarique · Answer 1 · 2018-11-29T06:44:07.833

0

I am little bit confused about your tables structure however i tried to answer it assuming you are storing answers and answering users_id in answers table -

Update: Thanks for providing table structure please see below update query.

SELECT questions.*, questions_answers.* 
FROM questions 
LEFT JOIN questions_answers ON questions.q_id = questions_answers.q_id 
WHERE questions_answers.user_id <> '2'

OR

SELECT questions.*, questions_answers.* 
FROM questions 
LEFT JOIN questions_answers ON questions.q_id = questions_answers.q_id 
WHERE NOT(questions_answers.user_id = '2')

edited Nov 29 '18 at 06:44

answered Nov 29 '18 at 06:34

Muhammad Tarique

1,407
1
13
17

I'll upload my table structure and sample data in a few minutes – jaegyo23 Nov 29 '18 at 06:35
Hi, I just added the table structure – jaegyo23 Nov 29 '18 at 06:40
@jaegyo23 I've just updated my answer please check and try. – Muhammad Tarique Nov 29 '18 at 06:44
Hi, sorry to confuse you. Looks like these blocks of code you suggested where able to fetch the data that has answered by another user. :( – jaegyo23 Nov 29 '18 at 06:54
I appreciate the effort :) I am able to get the answer using a slight change on the block of code I did. I will upload the answer I did on my own so that the readers will be able to know the correct one :) Thank you! – jaegyo23 Nov 29 '18 at 06:56
@jaegyo23 Yes that is what you had ask – Muhammad Tarique Nov 29 '18 at 06:56
@jaegyo23 Good you did it on your own but spend more time and think wisely while structuring your table because writing simple and fast query is better than the complex one. – Muhammad Tarique Nov 29 '18 at 06:59
Thank you for the advice. :) I've been stuck on this for a few days now, only to find out I did it on my own because of you & other's suggestions too. – jaegyo23 Nov 29 '18 at 07:02
I'll appreciate it if you will upvote the answer if you'll be able to try it :) – jaegyo23 Nov 29 '18 at 07:04

score 0 · Answer 2 · answered Nov 29 '18 at 06:43

0

You can use left join with

SELECT a.*
FROM questions as a 
 LEFT JOIN questions_answers as b ON (a.q_id = b.q_id)
WHERE  b.q_id IS NULL 
AND b.user_id = 2

Please check below link How to select all records from one table that do not exist in another table?

answered Nov 29 '18 at 06:43

Justin J

808
8
14

I am able to get the answer using a slight change on the block of code I did. I will upload the answer I did on my own so that the readers will be able to know the correct one. Thank you for your answer – jaegyo23 Nov 29 '18 at 06:56

score 0 · Answer 3 · answered Nov 29 '18 at 07:00

And so, after a few trials (thanks to everyone who posted their suggestions), I was able to manage to get the data I am wanting for using the following block of code. :)

SELECT
   a.* 
FROM
   questions as a 
LEFT JOIN
   questions_answers as b 
   ON (a.q_id = b.q_id) AND b.user_id = 2
WHERE
   b.q_id IS NULL 
ORDER BY RAND() 
LIMIT 10

Sharing this so everyone who stumbles down on my post will get the correct answer. I'll appreciate if you will upvote this one if you have tried the code and got the answer. :)

Sumesh TG · Answer 4 · 2018-11-29T07:39:51.563

0

SELECT
   a.* 
FROM
   questions as a 
WHERE
  a.q_id not in(select q_id from questions_answers where user_id = 2) 
ORDER BY
   RAND() LIMIT 10

You can achieve this by simple inner query. I hope this will help you.

sqlfiddle : http://sqlfiddle.com/#!9/fee4ba/8

Or

SELECT * FROM (SELECT
   a.* ,b.ans_id
FROM
   questions as a 
   left JOIN
      questions_answers as b 
      ON (a.q_id = b.q_id) AND b.user_id = 2 ) as c
WHERE c.ans_id IS null
ORDER BY
   RAND() LIMIT 10

edited Nov 29 '18 at 07:39

answered Nov 29 '18 at 07:09

Sumesh TG

2,557
2
15
29

I posted my answer which got the data I needed. :) Thank you for the effort and as much as I want to upvote your answer because it worked for me too, I can't but just letting people know your answer worked as well. :) – jaegyo23 Nov 29 '18 at 07:19
@jaegyo23 do want to achieve this result using only join query? – Sumesh TG Nov 29 '18 at 07:21
I'm not thinking of limiting it to only join query.. does that affect the data being retrieved? – jaegyo23 Nov 29 '18 at 07:26

Fetch questions that the logged in user hasn't answered yet using PHP and MYSQL

4 Answers4