Why only records in 1 table from my database is being fetched

Question

I am new to programming. I can't seem to fix it after how many hours as I am still student and this is for my thesis.

I am trying to fetch the records from two tables

1. prisoner table
2. visitations table - this table has prisoner_id foreign key so If I click a prisoner it will also show his visitation records.

Here's my code:

<?php

include "connect.php";

$id = $_GET['id'];

$result = $connect->query(
    "SELECT * FROM prisoner                                                                
       INNER JOIN visitations ON prisoner.prisoner_id = visitations.prisoner_id
       WHERE prisoner.prisoner_id = $id"
) or die($connect->error);

$visit = $connect->query(
     "SELECT visitor,date_of_visit,time_of_visit,affinity,homeAddress FROM visitations 
        INNER JOIN prisoner ON prisoner.prisoner_id = visitations.prisoner_id 
        WHERE visitations.prisoner_id = $id"
) or die($connect->error);

PRISONER TABLE

while($row = $result->fetch_assoc()){

    $id = $row['prisoner_id'];
    $photo = $row['photo'];
    $gname = $row['givenName'];
    $mname = $row['middleName'];
    $lname = $row['lastName'];
    $aname = $row['nickname'];

<div class="row text-center">
                <?php echo "<img style = 'width: 16%;height: 16%;margin-top:30px;'src='images/".$photo."' >";  
                        echo "<br><br>$gname $mname $lname";
                ?>
           </div>

            <?php

            echo "<div class='col-lg-3'>Nickname: $aname </div>";
}

VISITATIONS

<h4>Visitations</h4>
            </div>

            <table border = 1>
            <tr>
                <th>Visitor Name</th>
                <th>Date of Visit</th>
                <th>Time of Visit</th>
                <th>Affinity</th>
                <th>Home Address</th>
            <tr>

            <?php


            while($row2 = $visit->fetch_assoc()){

                $v_visitor = $row2['visitor'];
                $v_date = $row2['date_of_visit'];
                $v_time = $row2['time_of_visit'];
                $v_affinity = $row2['affinity'];
                $v_address = $row2['homeAddress'];

            echo "
             <tr>
                <td>$v_visitor</td>
                <td>$v_date</td>
                <td>$v_time</td>
                <td>$v_affinity</td>
                <td>$v_address</td>
             </tr>

             </table>";

             }

It only fetches the records from visitations table and also I don't know why the other records are not inside the HTML table.

This is what it displays:

Can someone point out the problem with my code, please

Answer 1

Only one active statement per connection can be used in this context, so you'll want to consume the entire result set of the first query with fetch_all before moving on to a new query.

$result1 = $connect->query(...)
$rows1 = $result1->fetch_all(MYSQLI_ASSOC);

$result2 = $connect->query(...)
$rows2 = $result2->fetch_all(MYSQLI_ASSOC);

foreach ($rows1 as $row) ...

foreach ($rows2 as $row) ...

It's also possible to execute multiple queries in a single batch using multi_query.

Answer 2

Next to the mentioned SQL injection problem (which boils down to: How do I properly encode a SQL query) there are more things that can go wrong and another one is a HTML injection problem (which boils down to: How do I properly encode HTML).

So in short: A HTML table can only be closed once, and it is always for the first matching closing </table> tag. As in the OPs code there is output of one closing table tag per row (inside the while body), after the first row the table is closed and further data is rendered following the rules of HTML on how to render text-data within tags that are out of scope (that is as text below the table here).

So not only do your SQL homework regarding the SQL injections, but first of all get comfortable on how to improve writing PHP template files. You could for example benefit from the <?= $variable ?> notation, and as this is HTML and not plain text, the more correct <?= htmlspecialchars($variable) ?> variant.