while loop in mysql inner join

Question

My code join 4 table using INNER JOIN. I can't customize loop while using inner join. How to control while clause so unwanted loop wouldn't happen.

My code output (student and school name is looping in every subject field)

I want to display like this where student and school loops at once.

MY PHP Code

<table id="customers">
    <?php
        $query=$con->prepare("SELECT subjectcomb.subjectid, subjectcomb.schoolid, student.student, school.school, subject.name 
        FROM ((( subjectcomb
        INNER JOIN school ON subjectcomb.schoolid=school.id)
        INNER JOIN student ON subjectcomb.schoolid=student.schoolID)
        INNER JOIN subject ON subjectcomb.subjectid=subject.id)");
        $query->execute();
        while ($row = $query->fetch(PDO::FETCH_ASSOC)){?>
            <tr>
                <td>
                    <?php echo $row['school']."->".$row['student']?>
                </td>
            </tr>
            <tr>
                <td>
                    <?php echo $row['name']?>
                    <input type="text" name="mark"></input>
                </td>           
            </tr>
        <?php } ?>
</table>

Adding an if judgement before the first <tr> and judge whether you have already output this schoool->student could solve this issue. But I guess you want to solve it just with SQL command? — Phil
– Phil, Commented Feb 2, 2018 at 3:08
Did you try using group by student.student, school.school in your query? — pgngp
– pgngp, Commented Feb 2, 2018 at 3:32
@Phil How to apply if clause in this case, please give example. — AnandaLC
– AnandaLC, Commented Feb 2, 2018 at 3:35
@AnandaLC, Lucas Borges just gave you an example. That's also what I mean. — Phil
– Phil, Commented Feb 2, 2018 at 3:49

Lucas Borges · Accepted Answer · 2018-02-02 03:47:24Z

Try adding an ORDER BY to your query:

$query=$con->prepare("SELECT subjectcomb.subjectid, subjectcomb.schoolid, student.student, school.school, subject.name 
        FROM ((( subjectcomb
        INNER JOIN school ON subjectcomb.schoolid=school.id)
        INNER JOIN student ON subjectcomb.schoolid=student.schoolID)
        INNER JOIN subject ON subjectcomb.subjectid=subject.id)
        ORDER BY school.school, student.student");

then in the while loop use this check:

<?php
$last_student = ""
while ($row = $query->fetch(PDO::FETCH_ASSOC)){
    if ($last_student != $row['student']) {
?>
    <tr>
        <td>
            <?php echo $row['school']."->".$row['student']?>
        </td>
    </tr>
    <?php } ?>
    <tr>
        <td>
            <?php echo $row['name']?>
            <input type="text" name="mark"></input>
        </td>           
    </tr>
<?php $last_student = $row['student'] } ?>

pgngp · Accepted Answer · 2018-02-02 04:01:12Z

0

I think what Phil meant was something like the following. You keep track of the previous school and student. If the current school and student are the same as the previous, you skip printing the school/student row, but if they not the same, you print them and also update the previous school's and previous student's values.

$prev_school = '';
$prev_student = '';
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
  if ($row['school'] != $prev_school && $row['student'] != $prev_student) { ?>
    <tr>
      <td>
        <?php echo $row['school']."->".$row['student'] ?>
      </td>
    </tr>
    <?php
    $prev_school = $row['school'];
    $prev_student = $row['student'];
  }
  ?>
  <tr>
    <td>
      <?php echo $row['name']?>
      <input type="text" name="mark"></input>
    </td>           
  </tr>
  <?php 
}

answered Feb 2, 2018 at 4:01

pgngp

1,5805 gold badges16 silver badges27 bronze badges

1 Comment

AnandaLC Over a year ago

Thanks for your great answer

Collectives™ on Stack Overflow

while loop in mysql inner join

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related