How to fetch and show these data in php mysql?

Question

I have question table and subject table and question table contain subject wise questions for online examination.

I need to fetch subject wise questions with subject name as header and show all the questions in subject wise serial number such as example: Maths: Q1, Q2, Q3 English: Q1, Q2, Q3 and so on. How to achieve it in php and mysql. The question table and subject table are given below.

Question sample data are given below

<?php 
  require_once 'config.php'; 
  //$con = mysqli_connect("localhost","root","","database_name");
  $query1 = "SELECT q.q_id,q.setq_no, q.qtext_eng, s.sub_id, s.sub_name
  FROM question q
  INNER JOIN subject s ON s.sub_id = q.sub_id
  INNER JOIN questionset qs ON qs.qset_id = q.qset_id
  WHERE qs.qset_id =2 ORDER BY s.sub_id";
  ?>
 <table class="table table-bordered">
 <thead>
 <tr>
  <th>Q.No</th>
  <th>Q Set number</th>
  <th>Q text eng</th>
</tr>

 <?php
$result1 = mysqli_query($link,$query1);

while($row1 = mysqli_fetch_array($result1))
 {
 $subID = $row1['sub_id'];
  $subName = $row1['sub_name'];
  ?>
  <h2><?php echo "$subName" ?></h2>

<?php
  error_reporting(0);


    $sno++;
    $qSet = $row1['setq_no'];
    $qEng = $row1['qtext_eng'];

?>
<tr>
  <td><?php echo $sno; ?></td>
  <td><?php echo $qSet; ?></td>
  <td><?php echo $qEng; ?></td>
</tr>
 </tbody>
 </table>
     <?php
   }
 ?>

Please post some minimalistic textual sample data, expected output and what you've tried so far — Gurwinder Singh, Dec 24 '16 at 03:33
and accept answers that solved your other questions that were solved — Funk Forty Niner, Dec 24 '16 at 03:36
Well, show your code, have you tried anything yet. Here you can only get help you If you have tried anything. — Kumar, Dec 24 '16 at 03:40
Possible duplicate of [how can i fetch data from database in php using mysql?](http://stackoverflow.com/questions/14827380/how-can-i-fetch-data-from-database-in-php-using-mysql) — , Dec 24 '16 at 04:17
Too long. Readers' eye will be exhausted, and hardly recognize what you want actually. Please minimize your question. — Codemole, Dec 24 '16 at 07:21

ab29007 · Accepted Answer · 2016-12-24T07:12:07.833

2

I am including some of your columns from question table here , you can add the rest same way

<?php 
$con = mysqli_connect("localhost","root","","database_name");

$query1 = "SELECT q.q_id,q.setq_no, q.qtext_eng, s.sub_id, s.sub_name
FROM question q
INNER JOIN subject s ON s.sub_id = q.sub_id
INNER JOIN questionset qs ON qs.qset_id = q.qset_id
WHERE qs.qset_id =2 ORDER BY s.sub_id";

$presubID = 0;

<table class="table table-bordered">
while($row1 = mysqli_fetch_array($result1))
{
  $subID = $row1['sub_id'];
  if($subID != $presubID){
    $subName = $row1['sub_name'];
    <h2><?php echo "$subName" ?></h2>
    $sno=0;

      <thead>
        <tr>
          <th>Q.No</th>
          <th>Q Set</th>
          <th>Q text eng</th>
        </tr>
      </thead>
  }
  $presubID = $subID;

  $sno++;
  $qSet = $row1['setq_no'];
  $qEng = $row1['qtext_eng'];

  <tr>
    <td><?php echo $sno; ?></td>
    <td><?php echo $qSet; ?></td>
    <td><?php echo $qEng; ?></td>
  </tr>

<?php
  }
?>
</table>

edited Dec 24 '16 at 07:12

answered Dec 24 '16 at 04:10

ab29007

7,611
2
17
43

How can I fetch in a single mysqli query using inner join – Shaun Dec 24 '16 at 06:04
you should not in your case , because then the outer while loop will be useless and you'd have to run five separate joint queries on separate subjects. but anyways here's a good example: http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join_inner – ab29007 Dec 24 '16 at 06:12
Just like you gave me the code. I run it exactly found what I needed but I need to achieve this in a single query. and I need to show subject name single time but when I use inner join and I run the code subject name repeated times the question – Shaun Dec 24 '16 at 06:21
I run this code but got subject name repeated SELECT q.q_id,q.setq_no, q.qtext_eng, s.sub_id, s.sub_name as ss FROM question q INNER JOIN subject s ON s.sub_id = q.sub_id INNER JOIN questionset qs ON qs.qset_id = q.qset_id WHERE qs.qset_id =2 – Shaun Dec 24 '16 at 06:24
where did you add this query. can you take a screenshot of the code and post it on http://tinypic.com/ – ab29007 Dec 24 '16 at 06:29
try removing the outer while loop – ab29007 Dec 24 '16 at 06:30
I removed the outer while loop and shown using php but subject name is repeated on every question display – Shaun Dec 24 '16 at 06:37
Can you post pic of your page output and code for the page (like I gave in my answer) too. and give me some time to po=rocess those, thanks – ab29007 Dec 24 '16 at 06:40
ok can you give me some time and your inner join query was giving you correct result, right , the only problem is displaying right? – ab29007 Dec 24 '16 at 06:54

How to fetch and show these data in php mysql?

1 Answers1