how to get data from two tables Using JOIN

Question

question
**question_id  question  option1 option2 option3 option4** 
    15          AAA         a1     a2      a3       a4
    38          BBB         b1     b2      b3       b4
    39          CCC         c1     c2      c3       c4
    44          DDD         d1     d2      d3       d4
    45          EEE         e1     e2      e3       e4

exam_question_list
**id**  **exam_paper_id** **category_id** **job_id**   **q1  q2  q3  q4  q5**
  1          1                    3         1            15  38  39  44   45

MY query $i=1;

 $sql="SELECT * FROM exam_question_list eq 
LEFT JOIN question qu1 ON qu1.question_id =eq.q1 
LEFT JOIN question qu2 ON qu2.question_id =eq.q2 
LEFT JOIN question qu3 ON qu3.question_id =eq.q3 
LEFT JOIN question qu4 ON qu4.question_id =eq.q4 
LEFT JOIN question qu5 ON qu5.question_id =eq.q5";

$result=mysqli_query($dbcon,$sql); ?>
    <table>

<?php 
    while($row = mysqli_fetch_array($result)){
?>
    <tr>
     <tr data-label="Question"><td><input name="q<?php echo $i++; ?>" value="<?php  echo $row['question'];?>" readonly /></td></tr>
      <tr data-label="Question"><td><input type="radio"  name="a" value="1"/><?php echo $row['option1']; ?></td></tr>
      <tr data-label="Question"><td><input type="radio"  name="a" value="2"/><?php echo $row['option2']; ?></td></tr>
      <tr data-label="Question"><td><input type="radio"  name="a" value="3"/><?php echo $row['option3']; ?></td></tr>
      <tr data-label="Question"><td><input type="radio"  name="a" value="4"/><?php echo $row['option4']; ?></td></tr>  

    </tr>   
<?php
    }
    ?>
</table>

This gives only the last question(q5) only. I want to display the q1,q2,q3,q4,q5 question and its options separately. How to do that?

where are q1 through q5 coming from? do you have columns in the first table that are named that? — CodingInTheUK
– CodingInTheUK, Commented Mar 22, 2017 at 7:18
FOREIGN KEY (q1) REFERENCES question(question_id), FOREIGN KEY (q2) REFERENCES question(question_id), FOREIGN KEY (q3) REFERENCES question(question_id), FOREIGN KEY (q4) REFERENCES question(question_id), FOREIGN KEY (q5) REFERENCES question(question_id), — Ravindu Theekshana
– Ravindu Theekshana, Commented Mar 22, 2017 at 7:22

Community · Accepted Answer · 2017-05-23 11:46:22Z

1

Use aliases to distinguish each instance of the question table. If you like, you can include the optional AS keyword.

SELECT * FROM exam_question_list AS eq 
LEFT JOIN question AS qu1 ON qu1.question_id =eq.q1 
LEFT JOIN question AS qu2 ON qu2.question_id =eq.q2 
LEFT JOIN question AS qu3 ON qu3.question_id =eq.q3 
LEFT JOIN question AS qu4 ON qu4.question_id =eq.q4 
LEFT JOIN question AS qu5 ON qu5.question_id =eq.q5

Source here

In get Options too

SELECT *,question.option1,question.option2,question.option3,question.option4 FROM exam_question_list AS eq 
LEFT JOIN question AS qu1 ON qu1.question_id =eq.q1 
LEFT JOIN question AS qu2 ON qu2.question_id =eq.q2 
LEFT JOIN question AS qu3 ON qu3.question_id =eq.q3 
LEFT JOIN question AS qu4 ON qu4.question_id =eq.q4 
LEFT JOIN question AS qu5 ON qu5.question_id =eq.q5

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Mar 22, 2017 at 7:29

Nawin

1,6922 gold badges14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ravindu Theekshana Over a year ago

can give an example, I can't understand. How to use 'As' keyword, can give some link of example

Ravindu Theekshana Over a year ago

Query is ok. But still I can't get the all questions, options into the page, what is the error in while loop? how can I preview the q1-q5 all 5 questions with options.

Nawin Over a year ago

So you need to get all from question table also?

Ravindu Theekshana Over a year ago

not all, question, option1,option2,option3,option4 from 'question' table

Nawin Over a year ago

Try This code

SELECT * FROM question AS q  LEFT JOIN exam_question_list AS eq ON eq.q1 = q.question_id AND eq.q2 = q.question_id AND eq.q3 = q.question_id AND eq.q4 = q.question_id AND eq.q5 = q.question_id

|

Collectives™ on Stack Overflow

how to get data from two tables Using JOIN

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related