How to add a button to my PHP form that deletes rows from my MYSQL database

Question

I am new to php coding.

I am adding each row delete button, but it should not working.

This is my html code:

<!DOCTYPE html>
<html>
<head>
</head>
<body>
<?php
    $connection = mysql_connect('localhost', 'root','');
if (!$connection)
{
    die("Database Connection Failed" . mysql_error());
}
$select_db = mysql_select_db( "emp",$connection);
if (!$select_db)
{
    die("Database Selection Failed" . mysql_error());
}
    $sql = "SELECT * FROM venu ";     
    $result = mysql_query($sql) or die(mysql_error());
    ?>
    <table border="2" style= " margin: 0 auto;" id="myTable">
      <thead>
        <tr>
          <th>name</th>
          <th>id</th>
          <th>rollnumber</th>
          <th>address</th>
          <th>phonenumber</th>
        </tr>
      </thead>
      <tbody>
      <?php
            while($row = mysql_fetch_array($result))
            {
              echo "<tr>";
                echo "<td>" . $row['name'] . "</td>";
                echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . $row['rollnumber'] . "</td>";
                echo "<td>" . $row['address'] . "</td>";
                echo "<td>" . $row['phonenumber'] . "</td>";
               echo "<td><form action='delete.php' method='POST'><input type='hidden'  value='".$row["address"]."'/><input type='submit' name='submit-btn' value='delete' /></form></td></tr>";
                echo "</tr>";

                
            }
            ?>
            </tbody>
            </table>
    </body>
    </html>

This is my delete code:

<?php
$connection = mysql_connect('localhost', 'root','');
if (!$connection)
{
    die("Database Connection Failed" . mysql_error());
}
$select_db = mysql_select_db( "emp",$connection);
if (!$select_db)
{
    die("Database Selection Failed" . mysql_error());
}
    error_reporting(0);
    session_start();
    $name = $_POST['name'];
    $id = $_POST['id'];
    $rollnumber = $_POST['rollnumber'];
    $address = $_POST['address'];
    $phonenumber = $_POST['phonenumber'];
    if($name!='' and $id!='')
    {
        $sql = mysql_query("DELETE FROM 'venu' WHERE name='balaji'AND id='93'AND rollnumber='93'AND address='bangalore'AND phonenumber='1234567890'");
    echo "<br/><br/><span>deleted successfully...!!</span>";
}
else{
echo "<p>ERROR</p>";
}
mysql_close($connection); 
?>

I am trying to delete each row using a button, but it is not working.

Tables/columns should be in backticks. You probably don't need all those parameters in the `where` clause, isn't `id` unique? You aren't passing any values to the query. — chris85, Nov 08 '16 at 05:17

Razib Al Mamun · Accepted Answer · 2016-11-08T05:39:58.467

In your html view page some change echo "<td><a href='delete.php?did=".$row['id']."'>Delete</a></td>"; like bellow:

<?php
while($row = mysql_fetch_array($result))
{
    echo "<tr>";
        echo "<td>" . $row['name'] . "</td>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['rollnumber'] . "</td>";
        echo "<td>" . $row['address'] . "</td>";
        echo "<td>" . $row['phonenumber'] . "</td>";
        echo "<td><a href='delete.php?did=".$row['id']."'>Delete</a></td>";
    echo "</tr>";
}
?>

PHP delete code :

<?php
if(isset($_GET['did'])) {
    $delete_id = mysql_real_escape_string($_GET['did']);
    $sql = mysql_query("DELETE FROM venu WHERE id = '".$delete_id."'");
    if($sql) {
        echo "<br/><br/><span>deleted successfully...!!</span>";
    } else {
        echo "ERROR";
    }
}
?>

Note : Please avoid mysql_* because mysql_* has beed removed from PHP 7. Please use mysqli or PDO.

More details about of PDO connection http://php.net/manual/en/pdo.connections.php

And more details about of mysqli http://php.net/manual/en/mysqli.query.php

Should add a note about SQL injections. – chris85 Nov 08 '16 at 05:25 — chris85, Nov 08 '16 at 05:25
i have added `mysql_real_escape_string($_GET['did']);` – Razib Al Mamun Nov 08 '16 at 05:28 — Razib Al Mamun, Nov 08 '16 at 05:28

score 1 · Answer 2 · edited May 23 '17 at 12:01

First you need to change your button like

echo "<td><a href="delete.php?id='.$row['id'].'">Delete</a></td>";

this will send the ID of the row which you want to delete to the delete.php

Secondly you need to change a bit your delete.php currently is wide open for SQL injections. Try using MySQLi or PDO instead

if(isset($_GET['id'])) {

    $id = $_GET['id'];

    $stmt = $mysqli->prepare("DELETE FROM venu WHERE id = ?");
    $stmt->bind_param('i', $id);
    $stmt->execute(); 
    $stmt->close();
}

Of course if you need to add more parameters in delete query you should pass them also with the button..

EDIT: Simple example for update record

You can put second button on the table like

echo "<td><a href="update.php?id='.$row['id'].'">Update</a></td>";

Then when you click on it you will have the ID of the record which you want to update. Then in update.php

if(isset($_GET['id'])) {

    $id = $_GET['id'];
    $stmt = $mysqli->prepare("UPDATE venu SET name = ?, rollnumber = ?, address = ? WHERE id = ?");
    $stmt->bind_param('sisi', $name, $rollnumber, $address, $id);
    $stmt->execute(); 
    $stmt->close();
}

Here ( in update.php ) you can have form which you can fill with new data and pass to variables $name, $rollnumber, $address then post it to update part.

Something to start with: PHP MySqli Basic usage (select, insert & update)

$sql = mysql_query("UPDATE 'venu' SET name='$name'AND id='$id'AND rollnumber='$rollnumber'AND address='$address' phonenumber='$phonenumber'"); it is correct — Dep, Nov 08 '16 at 07:43

score -1 · Answer 3 · answered Nov 20 '19 at 15:19

change up your query to use the dynamic value entered by the user, right now it is hard coded in there.

session_start();

require_once 'conn.php';

class myClass extends dbconn {
  public function myClassFunction(){

    try {
      $id = $_GET['id'];
      if(isset($_GET['id'])) {

          $sql = "DELETE FROM tablename WHERE id = ?";

          $stmt = $this->connect()->query($sql);
          $stmt->bind_param('i', $id);
          header("location: ../filepath/index.php");
      }
    } catch (PDOException $e) {
      echo $sql . "<br>" . $e->getMessage();
    }

  }
}

Ricardo Ortega Magaña · Answer 4 · 2016-11-08T05:27:07.837

-2

This line is wrong, you need to set the WHERE clause to the data you get from the hidden input value

$sql = mysql_query("DELETE FROM 'venu' WHERE name='balaji'AND id='93'AND rollnumber='93'AND address='bangalore'AND phonenumber='1234567890'");

Should be:

$sql = mysql_query("DELETE FROM 'venu' WHERE address='"._POST['address']."'");

And in the little form you are using, change:

<input type='hidden'  value='".$row["address"]."'/>

to:

<input type='hidden' name='address' value='".$row["address"]."'/>

edited Nov 08 '16 at 05:27

answered Nov 08 '16 at 05:18

Ricardo Ortega Magaña

2,403
16
23

`FROM 'venu' WHERE adress` is never going to work, also SQL injections. Also why would you delete on the address and not the `id`? – chris85 Nov 08 '16 at 05:19
Dude, ask that to the OP, im answering his question. – Ricardo Ortega Magaña Nov 08 '16 at 05:22
`adress` is not a column, the form is created incorrectly. Deleting on an address is dangerous as is deleting with `$_POST` directly. – chris85 Nov 08 '16 at 05:23

How to add a button to my PHP form that deletes rows from my MYSQL database

4 Answers4