CODEWITHSUNDEEP
phpmysqljoin
Ravindu Theekshana
Ravindu Theekshana

Reputation: 37

how to get data from two tables Using JOIN

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?

Upvotes: 2

Views: 75

Answers (1)

Nawin
Nawin

Reputation: 1692

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

Upvotes: 1

Related Questions