PHP While循环仅获取第一行结果

Question

i am fetching data from db in datatable , but my while loop is only fetching first row results . i can guess that i am wrong somewhere but not exactly know where . find below the details

Tables :

user :

user_id | user_role_id | user_name | fname | lname | profile
:-----: | :----------: | :-------: | :---: | :---: | :-----:
  1     |    2         |  schin    |  sam  |  chin |  t1.png
  2     |    2         |  mlouis   |  mark |  louis|  t2.png

teachers :

 t_id   |   classes    | subjects  | pri_classes | primary_subjects | email
:-----: | :----------: | :-------: | :---------: | :--------------: | :-----:
  1     |  eight,nine  |  math,phy |    nine     |       maths      |  [e-mail]
  2     |    two       |  science  |    two      |       science    |  [e-mail]

Php Query :

<?php 

$sql3 = "select * from user where user_role_id = 2";
$result3 = $dbh->query($sql3);
$row3 = mysqli_fetch_assoc($result3);

$user_id = $row3['user_id'];

$sql4 = "select * from teachers where t_id = '$user_id'";
$result4 = $dbh->query($sql4);

?>

Table head :

<table id="datatable-table" class="table table-striped table-hover">
 <thead>
  <tr>
  <th>S.no</th>
   <th class="no-sort hidden-sm-down">Image</th>
   <th >Name</th>
   <th >User Name</th>
   <th >Classes Handled</th>
   <th >Subjects Handled</th>
   <th >Primary Class</th>
   <th >Primary Subjects</th>
   <th >Email</th>
   </tr>
   </thead>
   <tbody>

While loop :

 <?php
 while(($row4 = mysqli_fetch_assoc($result4))&&($row3)){
 ?>
 <tr align="center">
 <td>
 <?php
 $i = 1;
 echo $i;
 $i++;
 ?>
</td>
<td>
<span >
<img src="../img/<?php echo $row3['profile'];?>" style="width:40px; height:40px;">
</span>
</td>
<td>
<span class="fw-semi-bold">
<?php echo $row3['fname'].' '.$row3['lname']; ?>
</span>
</td>
<td>
<span class="fw-semi-bold">
<?php echo $row3['user_name']; ?>
</span>
</td>
<td><span class="fw-semi-bold">
<?php 
$classes=explode(',', $row4['classes']);
$prefix = '';
foreach($classes as $cout)
{
echo $prefix . '' . wordsToNumber($cout);
$prefix = ', ';
}
?>
</span>
</td>
<td><span class="fw-semi-bold">
<?php 
$subjects=explode(',', $row4['subjects']);
$prefix = '';
foreach($subjects as $sout) {
echo $prefix . '' . str_replace('\' ', '\'', ucwords(str_replace('\'', '\' ', strtolower($sout))));
$prefix = ', ';
}
?>    
</span>
</td>
 <td>
<span class="fw-semi-bold">
<?php 
echo wordsToNumber($row4['pri_classes']);
?> 
</span></td>
<td><span class="fw-semi-bold">
<?php 
echo str_replace('\' ', '\'', ucwords(str_replace('\'', '\' ', strtolower($row4['primary_subjects']))));
?>   
</span></td>
<td><span class="fw-semi-bold"><?php echo wordwrap($row4['email'],10, "<br>
"); ?></span></td>
</tr>
<?php
}
?>  
</tbody>
</table>

For wordsToNumber i use this function

Answer 1

Your first query is only retrieving a single user. You need nested loops.

while ($row3 = mysqli_fetch_assoc($result3)) {
    $user_id = $row3['user_id'];

    $result4 = $dbh->query("select * from teachers where t_id = '$user_id'");

    while($row4 = mysqli_fetch_assoc($result4)){
        // display etc
    }
}

You may want to consider some joins in MySQL to avoid so many queries http://dev.mysql.com/doc/refman/5.7/en/join.html