PHP Select Query. Check value from 1 table matches another value from another table

[joeparkinson]

New to PHP got a quiz. Have a input form for answers that goes to a mysql table in db 15 Questions/15Answers. And also within the db a questions table an ID field that goes to 15 and a correct answer field.

Questions Table:

• ID - int 11 /PK / Not NULL
• question - text / Not NULL
• rightanswer - text / Not NULL

Answer Table:

• ID - int 11 / PK / Not NULL
• inputanswer1 - varchar 255 / Not NULL
• inputanswer2 - varchar 255 / Not NULL
• inputanswer3 - varchar 255 / Not NULL

I want to know using this method of checking from the questions table the correct answer which I’ve done below but issue I’m having is than matching that with another table where the input answers are stored.

Question is what’s the operator used to get saying ‘equal to’ something else and seeing if they match. And it’s this all in 1 select query.

Questions Table

SELECT fieldname (rightanswer) FROM tablename (Questions)  WHERE ID = 1;
SELECT fieldname FROM tablename WHERE Id = 2;

This above code is working and doing the first part I want it to do which is selecting the “rightanswer” from “Questions” table with the first question ID of “1” now need help with second part of matching/comparing that to where the answers are inputted which is in “Answers” table with starting with “inputanswer1”

Looking for does the “rightanswer” from “Questions” table match “inputanswer1” from “Answers” table.

??? Answers Table

SELECT fieldname FROM tablename;

[igor_g]

Questions Table:

• id- int 11 /PK / Not NULL
• question - text / Not NULL

Answer Table:

• id - int 11 / PK / Not NULL
• question_id - int 11 / Not NULL # FK to question.id
• text - varchar 255 / Not NULL
• is_right - smallint 1 / Not NULL

[joeparkinson]

Thanks for the response. So you say to create another field in answers table which is the same ID field from questions table and have it a FK. And than you saying have rightanswer instead within the questions table but in the answers table? Once done that what is then the new full select query?

[igor_g]

Once done that what is then the new full select query?

SELECT * FROM answer WHERE question_id = :id AND is_right = 1

or

SELECT question.*, answer.text
FROM question
INNER JOIN answer
ON question.id = :id
AND question.id = answer.question_id
AND answer.is_right = 1

[joeparkinson]

Thanks for the reply just want to go through this select statement and the values you mentioned.

@igor_g

SELECT * FROM answer WHERE question_id = :id AND is_right = 1

SELECT * FROM answer (What field is “answer” is that my “inputanswer1”)?
WHERE question_id (That’s the questionID set from the new field added onto the answers table)?
= :id (Not sure what this ID is being referred from/to Is is the id in answers table or simply have “:id”

[igor_g]

:id is param placeholder. If you have question with id = 1, you’ve got:

SELECT * FROM answer WHERE question_id = 1 AND is_right = 1

Then you get single record from table ‘answer’ with your right answer on question 1.