PHP MYSQL display sql results with foreach loop

Question

I have a list of submitted vehicle checks. The checks are organized and grouped by date. The table shows the date as well as who submitted that check. When I click view, it should show all the checks, status of checks, and comments for submitted checks for that date only.

I have the table set up as form, that when "view" is clicked, it acts as a submit button to POST to another php file to display that dates checks.

This shows the table where the submitted checks are listed out by date.

$checkssql = " SELECT * FROM $appname2 GROUP BY date ORDER BY date DESC";
$checksqry = mysqli_query($conn, $checkssql);

<form name="submittedchecks" id="submittedchecks" method="POST" action="view-submitted-check.php">
   <div class="table-responsive text-nowrap">   
      <table class="table" >
         <thead>
            <tr>
            <th>Date</th>
            <th>Submitted By</th>
            </tr>
         </thead>
         <tbody class="table-border-bottom-0">
            <?php while ($checks = mysqli_fetch_array($checksqry)) {{
            echo '<input type=hidden name="checkdate[]" value='. $checks['date']. '>';
            echo '<input type="text" name="appname" id="appname" readonly value="'.$appname2.'" hidden="hidden">';
            echo '<tr>';
            echo '<td> <input type="text" value="'.$checks['date'].'" name="date[]" id="date" style="border: none" readonly></td>';
            echo '<td>'.$checks['submitted_by'].'</td>';
            echo "<td> <button class='btn btn-info' type='submit' name='submitview' id='submitview'>View</button> </td> " ;}
            if($row['user_type']=='admin'){  echo "<td> <a href=delete-truck-check-script.php?id=".$checks['id']."><button class='btn btn-danger' type='button' >Delete Check</button></a> </td> " ;} 
echo'</tr>'; }?>
      </table>
   </div>
</form>

This is the code for getting the post data and displaying it in the table of the selected date of the check.

foreach ($_POST['checkdate'] as $index => $id){ 
    
$appname = $_POST['appname'];
$date = $_POST['date'][$index];
    
$viewlog = "SELECT * FROM $appname WHERE date = '$date' ORDER BY id";
$viewresult = mysqli_query($conn, $viewlog);
     
 }

<table class="table" >
   <thead>
      <tr>
      <th>Check Name</th>
      <th>Status</th>
      <th>Comment</th>
      </tr>
   </thead>
   <tbody class="table-border-bottom-0">
      <?php while ($checkview = mysqli_fetch_array($viewresult)) {{
      echo '<tr>';
      echo '<td>' .$checkview['check_name']. '</td>';
      if($checkview['status']=='Pass'){  echo "<td> <label class='btn rounded-pill btn-outline-success'>Pass</label> </td> " ;} 
      if($checkview['status']=='Warning'){  echo "<td> <label class='btn rounded-pill btn-outline-warning'>Warning</label> </td> " ;}
      if($checkview['status']=='Fail'){  echo "<td> <label class='btn rounded-pill btn-outline-danger'>Fail</label> </td> " ;}
      echo '<td>' .$checkview['comment']. '</td>';}
      echo'</tr>'; }?>
</table>

Ive tried multiple answers from other posts and nothing is working. I need another set of eyes to find my error.

I switched $id to $index, but its getting only 1 date. I clicked on the top check "03-21-2024", but its showing the check for "03-20-2024"

Answer 1

Since you're only interested in one specific date, you shouldn't put an array of inputs in the form. Instead, there should be a separate form in each row. The form can be entirely inside the <td> with the submit button, it just contains the appname hidden input, and the date can be in the value of the submit button.

$checkssql = " SELECT * FROM $appname2 GROUP BY date ORDER BY date DESC";
$checksqry = mysqli_query($conn, $checkssql);
?>
<div class="table-responsive text-nowrap">   
  <table class="table" >
     <thead>
        <tr>
        <th>Date</th>
        <th>Submitted By</th>
        </tr>
     </thead>
     <tbody class="table-border-bottom-0">
        <?php while ($checks = mysqli_fetch_array($checksqry)) {
            echo '<tr>';
            echo '<input type=hidden name="checkdate[]" value='. $checks['date']. '>';
            echo '<input type="text" name="appname" id="appname" readonly value="'.$appname2.'" hidden="hidden">';
            echo '<td>'.$checks['date'].'</td>';
            echo '<td>'.$checks['submitted_by'].'</td>';
            echo "<td> <form method='post' action='view-submitted-check.php'><input type='hidden' name='appname' value='".$appname2.".><button class='btn btn-info' type='submit' name='submitview' value='".$checks['date']."'>View</button></form> </td> " ;
            if($row['user_type']=='admin'){  echo "<td> <a href=delete-truck-check-script.php?id=".$checks['id']."><button class='btn btn-danger' type='button' name='delete' value='".$checks['date']."'>Delete Check</button></a> </td> " ;} 
            echo'</tr>';
        }?>
      </tbody>
  </table>
</div>

Then when processing the form submission, you don't have to loop over the parameters. There will just be one $_POST['submitview'] containing the date of the row they clicked on.

<?php
$appname = $_POST['appname'];
if (!valid_appname($appname)) {
    die("Invalid appname");
}

$date = $_POST'submitview'];
$stmt = mysqli_prepare($conn, "SELECT * FROM $appname WHERE date = ? ORDER BY id");
mysql_stmt_bind_param($stmt, "s", $date);
mysqli_stmt_execute($stmt);
$viewresult = mysqli_stmt_get_result($stmt);
?>
<table class="table" >
   <thead>
      <tr>
      <th>Check Name</th>
      <th>Status</th>
      <th>Comment</th>
      </tr>
   </thead>
   <tbody class="table-border-bottom-0">
      <?php while ($checkview = mysqli_fetch_array($viewresult)) {
          echo '<tr>';
          echo '<td>' .$checkview['check_name']. '</td>';
          if($checkview['status']=='Pass'){  echo "<td> <label class='btn rounded-pill btn-outline-success'>Pass</label> </td> " ;} 
          elseif($checkview['status']=='Warning'){  echo "<td> <label class='btn rounded-pill btn-outline-warning'>Warning</label> </td> " ;}
          elseif($checkview['status']=='Fail'){  echo "<td> <label class='btn rounded-pill btn-outline-danger'>Fail</label> </td> " ;}
          echo '<td>' .$checkview['comment']. '</td>';
          echo'</tr>';
       }
      ?>
   </tbody>
</table>