CODEWITHSUNDEEP
phpmysql
Babiker
Babiker

Reputation: 18798

How to fetch data from more than one table at once?

I have two tables in a database. Tablequestions, has two columns,ID and question. Table two answers has two columns question_ID and answer. In table answers there more than one answer pertaining to a question. So there are duplicate question_IDs. How do i display all questions from table questions with all the answers pertaining to it from table answers right under the question? Like:

How far is the sun?

The sun is 18 million miles away.
The sun is 93 million miles away.
The sun is 2 miles away.

How big is the sun?

Big.
Small.
Medium.

etc...

Sorry if the question seems a bit vague but its the best way i can explain it. Thanks in advance.

Upvotes: 2

Views: 380

Answers (3)

Andrew Cooper
Andrew Cooper

Reputation: 32576

You could do a join query on the two tables, and then parse the recordset to display the question/answer groups.

SELECT * 
FROM questions inner join answers
    ON questions.ID = answers.question_ID
ORDER BY questions.ID

Then for each record, if the question is different than in the previous record, print the new question and then the answer. If the question is the same as the previous record, just print the new answer.

Upvotes: 2

emurano
emurano

Reputation: 973

You would execute a select query that joins the two tables, ordering by the question id.

SELECT q.ID as question_id, q.question, a.ID as answer_id, a.answer
FROM questions q
LEFT JOIN answers a ON a.question_ID = q.ID
ORDER BY q.ID, a.ID

This will give you a result set like:

question_id   question                 answer_id   answer
1             What color is the sky?   100         Blue
1             What color is the sky?   101         Red
2             How old is dirt          102         Very Old
2             How old is dirt          103         Kinda Old

etc

When you loop through this result set you need to keep track of what the last question was so that when you go to the third result, question 2, you know to display the next question.

e.g.

$PreviousQid = null;
foreach ($QueryResult as $QueryRow)
{
   if ($PreviousQid === null || $PreviousQid != $QueryRow['question_id'])
   {
       echo "<b>" . $QueryRow['question'] . "</b><br/>\n";
   }

   echo $QueryRow['answer'] . "</br>\n";

   $PreviousQid = $QueryRow['question_id'];
}

Upvotes: 6

duffymo
duffymo

Reputation: 308743

Sounds like Question and Answer have a one-to-many relationship. The Answer table ought to have a boolean column that indicates which one is correct.

The Answer table will have a foreign key that points back to its parent Question. You'll get all the answers for a question using a JOIN.

There needs to be two tables in your database. How you render them in a UI is a separate issue. Keep them that way.

Upvotes: 3

Related Questions