MySQL：使用PHP Button删除一行

I am trying to create a delete button for the displayed rows. Those rows are actual data in MySQL Database.

我正在尝试为显示的行创建一个删除按钮。这些行是MySQL数据库中的实际数据。

When I ran my code, I am able to start the session, see all the data being displayed. But when I click on the Delete button, the data doesn't change inside the database.

当我运行我的代码时,我能够启动会话,查看显示的所有数据。但是当我单击“删除”按钮时,数据库内的数据不会发生变化。

I'm suspecting is the "value" of the Delete button. I am not sure how to associate that value with the sql query.

我怀疑是删除按钮的“值”。我不知道如何将该值与sql查询相关联。

Any help would be hugely appreciated. Thank you!

任何帮助将非常感激。谢谢!

<html>
<head>
<title>Delete Transaction</title>
</head>
<body>
<?php
session_start();
if (isset($_SESSION['Username'])) {
    $Username=$_SESSION['Username'];    
}
?>
<h2><?php echo "USER $Username LOGGED IN"; ?></h2>

<form action ="" method = "post">
<?php 

$dbc=mysqli_connect('localhost','testuser','password','Project')
 or die ("Could not Connect! \n");

$sql_query = "SELECT MemberID FROM Members WHERE Username = '$Username';";
$result1 = mysqli_query($dbc,$sql_query) or die ("error querying database");
if (mysqli_num_rows($result1) > 0 ) {
    $row = mysqli_fetch_assoc($result1);
    $mID = $row['MemberID'];
} // assigning mID as MemberID

$sql = "SELECT * FROM Sales WHERE Members_ID = '$mID' "; // Getting Members_ID in Sales Table
$result=mysqli_query($dbc,$sql) or die ("Error Querying Database");

$sql_getSalesID = "SELECT SalesID FROM Sales WHERE Members_ID ='$mID'"; // Getting SalesID
$result2 = mysqli_query($dbc,$sql_getSalesID) or die ("Error Querying Database 2");
if (mysqli_num_rows($result2) > 0 ) {
    $row = mysqli_fetch_assoc($result2);
    $SalesID = $row['SalesID'];
}

if (isset($_POST['SalesID']) and is_numeric($_POST['SalesID']))
{
    $delete =$_POST['SalesID'];
    $sql_delete="DELETE FROM Sales WHERE SalesID = '$delete'";
    $result3 = mysqli_query($dbc,$sql_delete) or die ("Error Querying Database 3");
}


echo "<table>";
echo "<tr> <th> User </th>  <th> Item </th> <th> Purchase Date </th> <th> Delete Option </th> </tr>";
while($row=mysqli_fetch_array($result)){
    echo "<tr> <td>".$row['Members_ID']."</td>
    <td>".$row['Items_ID']."</td> <td>".$row['PurchaseDate']."</td> 
    <td><button type='submit' name ='deleteTrans' value ='".$sql_delete."'> Delete </button></td>   </tr>";
}
echo "</table>";
 mysqli_close();
?>
</form>
</body>
</html>

1 个解决方案

#1

See the right solution for you: Delete this sale with $_GET I also added a firewall from attacks : SQL injection & XSS

请参阅适合您的解决方案:使用$ _GET删除此销售我还从攻击中添加了防火墙:SQL注入和XSS

Like this :

像这样 :

    <html>
<head>
<title>Delete Transaction</title>
</head>
<body>
<?php
session_start();
if (isset($_SESSION['Username'])) {
    $Username=$_SESSION['Username'];    
}
?>
<h2><?= "USER $Username LOGGED IN"; ?></h2>

<form action ="" method = "post">
<?php 

$db = @new mysqli('localhost','testuser','password','Project') or die ("Could not Connect! \n");

// Protect From SQL injection & XSS
if (isset($_GET)) {
       foreach ($_GET as $key => $value) {
         $_GET[$key] = $db->real_escape_string($value);
         $_GET[$key] = htmlspecialchars(trim($value), ENT_QUOTES, "utf-8");
       }
     }

     if (isset($_POST)) {
       foreach ($_POST as $key => $value) {
         $_POST[$key] = $db->real_escape_string($value);
         $_POST[$key] = htmlspecialchars(trim($value), ENT_QUOTES, "utf-8");
       }
     }

     if (isset($_REQUEST)) {
       foreach ($_REQUEST as $key => $value) {
         $_REQUEST[$key] = $db->real_escape_string($value);
         $_REQUEST[$key] = htmlspecialchars(trim($value), ENT_QUOTES, "utf-8");
       }
     }

//Your Code
$sql_query = "SELECT MemberID FROM Members WHERE Username = '$Username';";
$result1 = $db->query($sql_query) or die ("error querying database");
if (mysqli_num_rows($result1) > 0) {
    $row = $result1->fetch_assoc();
    $mID = $row['MemberID'];
} // assigning mID as MemberID

$sql = "SELECT * FROM Sales WHERE Members_ID = '$mID' "; // Getting Members_ID in Sales Table
$result=mysqli_query($db,$sql) or die ("Error Querying Database");

$sql_getSalesID = "SELECT SalesID FROM Sales WHERE Members_ID ='$mID'"; // Getting SalesID
$result2 = $db->query($sql_getSalesID) or die ("Error Querying Database 2");
if (mysqli_num_rows($result2) > 0 ) {
    $row = $result2->fetch_assoc();
    $SalesID = $row['SalesID'];
}

// Check if click del button
if (isset($_GET['del']) && is_numeric($_GET['del']))
{
    $del = $_GET['del'];
    $sql_delete = "DELETE FROM Sales WHERE SalesID = '$del'";
    $result3 = $db->query($sql_delete) or die ("Error Querying Database 3");
}

echo "<table>";
echo "<tr> <th> User </th>  <th> Item </th> <th> Purchase Date </th> <th> Delete Option </th> </tr>";
while($row = $result->fetch_assoc()){ ?>
    <tr><td><?= $row['Members_ID']; ?></td>
    <td><?= $row['Items_ID']; ?></td> <td><?= $row['PurchaseDate']; ?></td> 
    <td><a href="?del=<?= $row['SalesID']; ?>" class ='btn btn-danger'> Delete </a></td></tr>
}
<?
echo "</table>";

?>


</form>
</body>
</html>

#1