Deleting a Table Entry with PHP and SQL

Question

So I have created a form that takes in entries and stores them in a SQL database called Warehouse_Maitenence (yes I know it is mispelled and I did that on purpose). The goal for this set of code is to connect to the database and display the table accordingly

<?php include("nav.php") ?>
<body style='background-color:#C0C0C0'>
<style>
.top{
    text-align: Left;
}

.Headers_Inputs{
    padding-top: 20px;
    padding-bottom: 20px;
    border-style: solid;
    border-width: 1px;
}

input{
    width: 100%;
}
.PRIMARY_TABLE{
    padding-left: 20px;
    padding-right: 20px;
    width: 100%;
}

body{
    padding-left: 20px;
    padding-right: 20px;
    padding-top: 20px;
    padding-bottom: 60px;
}

.PRIMARY_TRAY{
    border-style: solid;
    border-width: 2px;
    }
</style>
<body>
<table width="100%" border="1">
<tr>
<th style="text-align: center; font-size: 16px;"> ID </th>
<th style="text-align: center; font-size: 16px;"> MACHINE </th>
<th style="text-align: center; font-size: 16px;"> LABEL </th>
<th style="text-align: center; font-size: 16px;"> CONDITION </th>
<th style="text-align: center; font-size: 16px;"> ACTION </th>
<th style="text-align: center; font-size: 16px;"> ADDRESS </th>
<th style="text-align: center; font-size: 16px;"> DATE </th>
<th style="text-align: center; font-size: 16px;"> COMMENT </th>
<th style="text-algin: center; font-size: 16px;"> DELETE </th>
</tr>
<?php
$username="";
$password="";
$database="";
$servername = "";
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = 'SELECT ID, MACHINE, LABEL, COND, ACTION, ADDRESS, DATE, COMMENT FROM Warehouse_Maitenence ORDER BY `Warehouse_Maitenence`.`MACHINE` ASC, `Warehouse_Maitenence`.`LABEL` ASC, `Warehouse_Maitenence`.`ID` ASC';
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td style='text-align: center; font-size: 16px;' id=".$row["ID"].">".$row["ID"]."</td>";
        echo "<td style='text-align: center; font-size: 16px;'>".$row["MACHINE"]."</td>";
        echo "<td style='text-align: center; font-size: 16px;'>".$row["LABEL"]."</td>";
        echo "<td style='text-align: center; font-size: 16px;'>".$row["COND"]."</td>";
        echo "<td style='text-align: center; font-size: 16px;'>".$row["ACTION"]."</td>";
        echo "<td style='text-align: center; font-size: 16px;'>".$row["ADDRESS"]."</td>";
        echo "<td style='text-align: center; font-size: 16px;'>".$row["DATE"]."</td>";
        echo "<td style='text-align: center; font-size: 16px;'>".$row["COMMENT"]."</td>";
        echo "<td style='text-align: center; font-size: 16px;'><input type='button' onclick=delete_entry(".$row["ID"].")</td>";
        echo "</tr>";
}
} else { echo "0 results"; }

echo "</table>";
mysqli_close($conn);
?>

So This level of code does work as intended up to the deletion point. I want to be able to load buttons on the end of each row of the table and onclick delete that row from the table and SQL Database.

For the table I have a JS already which works in another set of code I have been working on.

    function deleteRow(r) {
        var i = r.parentNode.parentNode.rowIndex;
        document.getElementById("dataTable").deleteRow(i);
}

I know that onclick I will need to reconnect to the database (no issues there since it is already in this code) and then run

DELETE FROM `Warehouse_Maitenence` WHERE `Warehouse_Maitenence`.`ID` = whatever row was selected

but I am not sure how to put it all together, since I am not doing a form submission.

Use an Ajax call in your `deleteRow` function and pass the ID to be deleted to the delete function you call using Ajax. — Dave, Oct 17 '19 at 16:39
to be honest I havent used AJAX specifically, im barely getting used to the SQL language — Alex Carlson, Oct 17 '19 at 16:40

score 1 · Accepted Answer · answered Oct 17 '19 at 20:11

1

Am only good with php and i will advice you to do this.. add this to the list of the table

<td>
<form action="delete.php" method="post" enctype="multipart/form-data">
 <input type="text" name="id" value="<?php echo $id; ?>" style="display:none;" />
 <input type="submit" name="delete" value="Delete" class="btn btn-danger" />
</form>
 </td>

then create a page call delete.php

    <?php
    if(!isset($_SERVER['HTTP_REFERER'])){ //This is to stop direct access to this delete.php page
    header('location: /404.php'); //where to be redirected to
    exit;
    }
    session_start();
    include("connect.php"); //your database connection declaration page

    if(isset($_POST['delete'])){ //name from the form from table
    $id = mysqli_real_escape_string($con, $_POST['id']); //id of the row

    $variable = $con->query("DELETE FROM tablename WHERE id = '$id'") or die(mysqli_error($con)); //connecting to the db to the table to delete 

    if($variable == TRUE){
         echo"<script>alert('Deletion Completed!');</script>";
         echo"<meta http-equiv='refresh' content='0 url=page.php' />"; //where to be redirected to after deleting.
        }else{
           echo"<script>alert('Error');</script>"; 
           print_r("Error, Lets go back and Try again");
         echo"<meta http-equiv='refresh' content='0 url=page.php' />";
        }
        exit();
}
?>

I hope am able to solve your issues.!

answered Oct 17 '19 at 20:11

Homezonic

234
2
6

I am going to be testing this out tomorrow but it makes sense, you manipulate the base code to generate a form at the end of each row and push the form to include the ID variable needed for the deletion. Then if I wanted the user to reload the original page (because it is a session, I just use header at the end of your code. – Alex Carlson Oct 18 '19 at 04:00
So I had to do some pretty hefty revisions to make this work but if someone knows how to read what you have given. I did adjust your code to make it a bit more dynamic by adding another input box in the table creation with the table name. Food for thought and thank you this was driving me nuts. – Alex Carlson Oct 18 '19 at 19:53
1

I'm glad am able to help you – Homezonic Oct 19 '19 at 00:38
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Oct 30 '19 at 22:27
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Oct 30 '19 at 22:27
Thankfully the functions are simple enough and I modified the input parameters so they are fixed and cannot be modified. If there was another option Dharman I'm open to it. Deleting entries on a button press based on ID or thankfully now randomly generated titles has allowed floor a boot more security. – Alex Carlson Feb 20 '20 at 07:59

Deleting a Table Entry with PHP and SQL

1 Answers1