I am using Laravel and MySQL. I need to get the next question to display from the database.
The user can be signed up to multiple forms. Each question can be shared between forms or be unique to one form.
Get the next question (by question order) in any form that matches the user ID that does not exist in answers.
Is there a way to do this in one database query? Or should I loop through to find the next unanswered question.
Here's a solution that should match what you described, returning the first question (by question_id) for any form that is not answered by a specific user (the user is a parameter you'd supply to a parameterized query):
SELECT f.form_id, f.question_id
FROM questions AS q
INNER JOIN forms AS f
ON f.question_id = q.id
LEFT OUTER JOIN answers AS a
ON a.question_id = q.id AND a.user_id = ?
WHERE a.question_id IS NULL
ORDER BY q.id LIMIT 1;
