PHP always fetching on last results only while fetching from HTML Table

Question

I'm currently new to PHP and I'm currently trying to develop a Workflow Approval Process Website, It works in a way where the user will submit a form and the form data will be inserted into MySQL database.

And on this approve.php , it will display the Form data fetched from the MySQL database based on user input, but it will only display the basic info such as FormID (unique), Name, Data etc as shown in [1]: https://i.stack.imgur.com/TmlJy.png

Moving on, I have added a button on the Status column, that will redirect user to another page (approveForm.php) to show the details of the Form, as shown in [2]: https://i.stack.imgur.com/GVApb.png

My initial doing was since my FormID is unique, I would pass this FormID variable to the next page, and then fetch all the details from database based on the FormID, example when user clicks on FormID number 39, it will redirect user to the approveForm.php page where it shows the details of Form #39.

But here's the issue, no matter which one I click, it only shows the details for the last result of the table from previous page which is Form #27. I hope that user can see the form details based on what FormID they clicked.

Below is my code for approve.php & approveForm.php

approve.php

<?php
session_start();
error_reporting(0);

$host = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "it_login";
$conn = new mysqli($host, $dbUsername, $dbPassword, $dbName);

$sql = "SELECT * FROM supportformuser WHERE Name = 'Admin12' ORDER BY Form_ID ASC ";

$results = mysqli_query($conn, $sql);

if (isset($_SESSION['id']) && isset($_SESSION['User_ID'])) {

  // session timeout (time calculated in seconds)
  if (time() - $_SESSION["login_time_stamp"] > 86400) {

    session_unset();
    session_destroy();
    header("Location:index.php");
    exit();
  }

?>
  <!DOCTYPE html>
  <html lang="en">

  <head>
    <link rel="stylesheet" type="text/css" href="approve.css">
    <link rel="stylesheet" type="text/css" href="//netdna.bootstrapcdn.com/font-awesome/4.0.3/css/font-awesome.min.css" />
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link href="https://fonts.googleapis.com/css2?family=Open+Sans&display=swap" rel="stylesheet">
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>

    <script src="approve.js"></script>

  </head>

  <body>
    <div class=all>
      <h1>Approval Process</h1>
      <div class="tbl-header">
        <table cellpadding="0" cellspacing="0">
          <thead>
            <tr>
              <th style="width: 10%;">Form ID</th>
              <th style="width: 15%;">Requested By</th>
              <th style="width: 10%;">Date </th>
              <th style="width: 20%;">Form Type</th>
              <th style="width: 35%;">Reason Of Request</th>
              <th style="width: 10%">Status</th>
            </tr>
          </thead>
        </table>
      </div>

      <form method="POST" class="tbl-content">
        <table cellpadding="0" cellspacing="0">
          <tbody>
            <?php while ($rows = mysqli_fetch_assoc($results)) {
            ?>
              <tr height="100px">
                <td style="width: 10%;" id="FormID" value="<?php echo $rows['Form_ID']; ?>">
                  <?php echo $rows['Form_ID']; ?>
                </td>
                <td style="width: 15%;"><?php echo $rows['Name']; ?></td>
                <td style="width: 10%;"><?php echo $rows['Date']; ?></td>
                <td style="width: 20%;"><?php echo $rows['Form_Type']; ?></td>
                <td style="text-align: justify; width:35%;" class="show-read-more">
                  <?php echo $rows['Reason_Of_Request']; ?>
                </td>
                <td style="width: 10%;">
                  <button type="submit" name="status" value=" <?php echo $rows['HOD_Status']; ?>">
                    <?php echo $rows['HOD_Status']; ?>
                  </button>

                </td>
              </tr>
            <?php
      

            if(isset($_POST['status'])) {
              
              // session_unset();
              unset($_SESSION['FormID']);
              $_SESSION['FormID'] = $rows['Form_ID'];
              header("Location: approveForm.php");
              
            }
                  
            }
            ?>
          </tbody>
        </table>
      </form>




      <div style="text-align:center;" class="logout" onclick="location.href = 'logout.php'">

        <button id="logout">LOG OUT</button>


      </div>

    </div>


  </body>

  </html>
<?php

} else {

  header("Location: index.php");
  exit();
}
?>

approveForm.php

<?php
session_start();

$host = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "it_login";
$conn = new mysqli($host, $dbUsername, $dbPassword, $dbName);

$formID = $_SESSION['FormID'];

$sql = "SELECT * FROM supportformuser WHERE Form_ID = '$formID' ";
$sql1 = "SELECT * FROM request_form WHERE Form_ID = '$formID' ";
$sql2 = "SELECT * FROM application_support_form WHERE Form_ID = '$formID' ";

$results = mysqli_query($conn, $sql);
$results1 = mysqli_query($conn, $sql1);
$results2 = mysqli_query($conn, $sql2);


if (isset($_SESSION['id']) && isset($_SESSION['User_ID'])) {


    // session timeout (time calculated in seconds)
    if (time() - $_SESSION["login_time_stamp"] > 86400) {

        session_unset();
        session_destroy();
        header("Location:index.php");
        exit();
    }

?>

    <!DOCTYPE html>
    <html lang="en">

    <head>
        <link rel="stylesheet" type="text/css" href="approveForm.css">
    </head>

    <body>
        <div class="all">
            <h1>Workflow Approval Process</h1>
            <div class="tbl-header">
                <table cellpadding="0" cellspacing="0">
                    <?php while ($rows = mysqli_fetch_assoc($results)) {
                    ?>
                        <thead>
                            <tr>
                                <th>Form ID</th>
                                <td> <?php echo $rows['Form_ID'] ?></td>
                            </tr>

                            <tr>
                                <th>User ID</th>
                                <td> <?php echo $rows['User_ID'] ?></td>
                            </tr>

                            <tr>
                                <th>Company</th>
                                <td> <?php echo $rows['Company'] ?></td>
                            </tr>

                            <tr>
                                <th>Date</th>
                                <td> <?php echo $rows['Date'] ?></td>
                            </tr>

                            <tr>
                                <th>Name</th>
                                <td> <?php echo $rows['Name'] ?></td>
                            </tr>

                            <tr>
                                <th>Department</th>
                                <td> <?php echo $rows['Department'] ?></td>
                            </tr>

                            <tr>
                                <th>Type of Form</th>
                                <td> <?php echo $rows['Form_Type'] ?></td>
                            </tr>

                            <?php while ($rows = mysqli_fetch_assoc($results1)) {
                            ?>
                                <tr>
                                    <th>Account Login & Access Right (Staff)</th>
                                    <td> <?php echo $rows['Staff_Status'] ?></td>
                                </tr>

                                <tr>
                                    <th>Account Login & Access Right (Login ID)</th>
                                    <td> <?php echo $rows['Account'] ?></td>
                                </tr>

                                <tr>
                                    <th>Access Right Requirement</th>
                                    <td> <?php echo $rows['Access_Right_Requirement'] ?></td>
                                </tr>

                                <tr>
                                    <th>IT Comment (For IT Technician Only)</th>
                                    <td> <?php echo $rows['IT_Comment_Account_Login_Access_Right'] ?></td>
                                </tr>

                                <tr>
                                    <th>Support Request (Software)</th>
                                    <td> <?php echo $rows['Software'] ?></td>
                                </tr>

                                <tr>
                                    <th>Support Request (Software) Requirement</th>
                                    <td> <?php echo $rows['Software_Requirement'] ?></td>
                                </tr>

                                <tr>
                                    <th>IT Comment (For IT Technician only)</th>
                                    <td> <?php echo $rows['IT_Comment_Software_Requirement'] ?></td>
                                </tr>

                                <tr>
                                    <th>Support Request (Hardware)</th>
                                    <td> <?php echo $rows['Hardware'] ?></td>
                                </tr>

                                <tr>
                                    <th>Support Request (Hardware) Requirement</th>
                                    <td> <?php echo $rows['Hardware_Requirement'] ?></td>
                                </tr>

                                <tr>
                                    <th>IT Comment (For IT Technician only)</th>
                                    <td> <?php echo $rows['IT_Comment_Hardware_Requirement'] ?></td>
                                </tr>

                                <tr>
                                    <th>Support Request (Tech Support Request)</th>
                                    <td> <?php echo $rows['Tech_Support_Request'] ?></td>
                                </tr>

                                <tr>
                                    <th>Support Request (Job) Requirement</th>
                                    <td> <?php echo $rows['Job_Requirement'] ?> </td>
                                </tr>

                                <tr>
                                    <th>IT Comment (For IT Technician only)</th>
                                    <td> <?php echo $rows['IT_Comment_Job_Requirement'] ?></td>
                                </tr>

                            <?php

                            }
                            ?>

                            <?php while ($rows = mysqli_fetch_assoc($results2)) {
                            ?>
                                <tr>
                                    <th>Application Support Form (Software)</th>
                                    <td> <?php echo $rows['Software'] ?> </td>
                                </tr>

                                <tr>
                                    <th>Application Support Form (Software) Remark</th>
                                    <td> <?php echo $rows['Software_Remark'] ?></td>
                                </tr>

                                <tr>
                                    <th>IT Comment (For IT Technician only)</th>
                                    <td> <?php echo $rows['IT_Comment_Software'] ?></td>
                                </tr>

                                <tr>
                                    <th>Application Support Form (Task)</th>
                                    <td> <?php echo $rows['Task'] ?></td>
                                </tr>

                                <tr>
                                    <th>Application Support Form (Task) Remark</th>
                                    <td> <?php echo $rows['Remark_For_Item2'] ?></td>
                                </tr>

                                <tr>
                                    <th>IT Comment (For IT Technician only)</th>
                                    <td> <?php echo $rows['IT_Comment_Task'] ?></td>
                                </tr>

                            <?php
                            }
                            ?>
                        </thead>
                    <?php
                    }
                    ?>
                </table>
            </div>
        </div>
    </body>

    </html>
<?php

} else {

    header("Location: index.php");
    exit();
}
?>

Please use prepared statements! your code is wide open to SQL injection. — Gert B., Aug 09 '21 at 10:40

score 0 · Accepted Answer · answered Aug 09 '21 at 10:48

You're looping the code in if(isset($_POST['status'])) { as many times are there are rows in your table, so you'll always set the $_SESSION['FormID'] = $rows['Form_ID']; value to the last value in the table. This code takes no account of which button was actually pressed in your form.

But using Session isn't the way to do this sort of task anyway. Instead of posting back and redirecting, just make a hyperlink to the approveForm page with the ID as a URL parameter.

For example:

You can replace

<button type="submit" name="status" value=" <?php echo $rows['HOD_Status']; ?>">
    <?php echo $rows['HOD_Status']; ?>
</button>

with

<a href="approveForm.php?id=<?php echo $rows['Form_ID']; ?>"><?php echo $rows['HOD_Status']; ?></a>

And then in approveForm.php, replace

$formID = $_SESSION['FormID'];

with

$formID = $_GET['FormID'];

so it reads the Form ID from the value passed in the URL.

(You can also remove the whole if(isset($_POST['status'])) { block).

P.S.

Warning: Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. Never insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data.

https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the mysqli documentation and this: How can I prevent SQL injection in PHP? . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values.

Also, never configure your web app to login to the database as root. Root can do whatever it likes, so on top of the SQL injection vulnerabilities this just leaves your database an open book for hackers. Instead create a separate user account specifically for this application which has only the permissions it actually needs in order to work properly. Don't even use the root account as a shortcut during development or testing, because you need to test your account permissions as well - otherwise when you go live you might have unexpected errors relating to the user account setup.

Thanks!!, my code works fine now, and as for the SQL Security issue, have also changed my code accordingly, thanks for the advice !! — Derek Tan, Aug 11 '21 at 02:03

PHP always fetching on last results only while fetching from HTML Table

1 Answers1