PHP / MySQL - 如何在已经自动生成的表中为一行自动生成删除链接？

Question

I have a PHP script that reads a database table and inserts all the rows into an HTML table until it's displayed all available rows as shown here:

require_once('dbconnect.php');

$sql = 
  "SELECT 
      ID, Site, Type, Requested, Quote,
      PO, CADs, MCS, DFP,
      SIM, Prereqs, Design, Report, Delivered 
    FROM Predictions";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  echo '<table class="table table-hover table-condensed">';

  while($row = $result->fetch_assoc()) {
    echo
      '<tbody>'.
        '<tr>'.
          '<td>'.$row['ID'].'</td>'.
          '<td>'.$row['Site'].'</td>'.
          '<td>'.$row['Type'].'</td>'.
          '<td>'.$row['Requested'].'</td>'.
          '<td>'.$row['Quote'].'</td>'.
          '<td>'.$row['PO'].'</td>'.
          '<td>'.$row['CADs'].'</td>'.
          '<td>'.$row['MCS'].'</td>'.
          '<td>'.$row['DFP'].'</td>'.
          '<td>'.$row['SIM'].'</td>'.
          '<td>'.$row['Prereqs'].'</td>'.
          '<td>'.$row['Design'].'</td>'.
          '<td>'.$row['Report'].'</td>'.
          '<td>'.$row['Delivered'].'</td>'.
          '<td>'.
            '<a href="#">'.
              '<span class="edit"><i class="fa fa-pencil"></i></span>'.
            '</a> | <a href="#">'.
              '<span class="delete"><i class="fa fa-times"></i></span>'.
            '</a>'.
          '</td>'.
        '</tr>'.
      '</tbody>';
  }

  echo "</table>";
}
else
  echo "0 results";

$conn->close();

That all works fine, but now I want to have what is essentially a delete button (you can see the markup above that creates the icon/link) that will populate automatically to correspond with the appropriate ID for the mysql database table. Image of table for visual idea of what I'm going for.

My delete script so far is below, but I have no idea what to put in the "WHERE id=", or how to incorporate it into my first script once it's setup properly.

<?php
require_once('dbconnect.php');

$sql = "DELETE FROM Predictions WHERE id=";

if($conn->query($sql) === TRUE)
  echo "Item deleted successfully";
else
  echo "Error deleting record; ". $conn->error;

$conn->close();

So basically I need advice to modify both of these scripts so that a delete link (or form, I don't care) is generated in the first script then applies the second script and it knows the corresponding id to use. In my search to solve this problem I saw some potential solutions using _GET, but in the same thread others said that is in fact a very bad and insecure solution.. so I'm very confused!

I'm learning PHP as I go, and I've only been going at it for about 2 days, so please have mercy :)

Answer 1

Change this

<a href='#'><span class='delete'>

to

<a href='deletepage.php?id=" . $row["ID"] . "'><span class='delete'>

then on "deletepage.php", whatever you are going to call that page do something like

require_once('dbconnect.php');
$id = (int)$_GET['id'];
$sql = "DELETE FROM Predictions WHERE id=" . $id;    
if($conn->query($sql) === TRUE) {
    echo "Item deleted successfully";
} else {
    echo "Error deleting record; ". $conn->error;
}
$conn->close();

I don't know what driver you are using here but the preferred solution would be using a prepared statement with a parameterized query.

So pretty much you send the id via a GET parameter to your "delete page". That page takes that value, casts it to an int to avoid SQL injections (read further below), and then deletes the data. You also could instead of echoing a success there use a header to redirect them to the previous page. You could append a GET parameter to that url display a success message. (or you could always do all this on the same page and just check if the id is being sent).

Also you should have this page behind someone secure login system. You don't want any user/bot able to execute that deletepage.php.

How can I prevent SQL injection in PHP?
http://php.net/manual/en/security.database.sql-injection.php
https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28Parameterized_Queries.29

I'm guessing you are using mysqli so take a look at this doc for prepared statements with that driver, http://php.net/manual/en/mysqli.quickstart.prepared-statements.php.