PHP Deleting Data In MySQL

Submitted by: 
Language: 
Visitors have accessed this post 1888 times.

PHP Deleting Data In MySQL

We already did in the tutorial for PHP Inserting Data To MySQL and PHP Select Data In MySQL. So, I decided to create a follow-up tutorial for PHP Deleting Data In MySQL.

We're gonna use Delete Statement to delete data from our database table.

"tbl_registration" Example Database Table.

This is the data.

Data

Deleting Data In MySQL Using MySQLi and PDO

Deleting the data tbl_registration_id = 2 in our "tbl_registration" table.

Deleting Record Using MySQLi (Object-Oriented)

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_query_pdo";
  6.  
  7. // Create connection
  8. $conn = new mysqli($servername, $username, $password, $dbname);
  9. // Check connection
  10. if ($conn->connect_error) {
  11. die("Connection failed: " . $conn->connect_error);
  12. }
  13.  
  14. // sql to delete a record
  15. $sql = "DELETE FROM tbl_registration WHERE tbl_registration_id = 2";
  16.  
  17. if ($conn->query($sql) === TRUE) {
  18. echo "Record deleted successfully";
  19. } else {
  20. echo "Error deleting record: " . $conn->error;
  21. }
  22.  
  23. $conn->close();
  24. ?>

Deleting Record Using MySQLi (Procedural)

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_query_pdo";
  6.  
  7. // Create connection
  8. $conn = mysqli_connect($servername, $username, $password, $dbname);
  9. // Check connection
  10. if (!$conn) {
  11. die("Connection failed: " . mysqli_connect_error());
  12. }
  13.  
  14. // sql to delete a record
  15. $sql = "DELETE FROM tbl_registration WHERE tbl_registration_id = 2";
  16.  
  17. if (mysqli_query($conn, $sql)) {
  18. echo "Record deleted successfully";
  19. } else {
  20. echo "Error deleting record: " . mysqli_error($conn);
  21. }
  22.  
  23. mysqli_close($conn);
  24. ?>

Deleting Record Using PDO (PHP Data Objects)

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_query_pdo";
  6.  
  7. try {
  8. $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  9. // set the PDO error mode to exception
  10. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  11.  
  12. // sql to delete a record
  13. $sql = "DELETE FROM tbl_registration WHERE tbl_registration_id = 2";
  14.  
  15. // use exec() because no results are returned
  16. $conn->exec($sql);
  17. echo "Record deleted successfully";
  18. }
  19. catch(PDOException $e)
  20. {
  21. echo $sql . "<br>" . $e->getMessage();
  22. }
  23.  
  24. $conn = null;
  25. ?>

In this tutorial, this is my simple source code and easy to understand on how to delete data in MySQL.

This is the Data Table and has a "Delete Record" button.

  1. <table border="1" cellspacing="5" cellpadding="5" width="100%">
  2. <tr>
  3. <th>No.</th>
  4. <th>First Name</th>
  5. <th>Middle Name</th>
  6. <th>Last Name</th>
  7. <th>Email</th>
  8. <th>Contact Number</th>
  9. <th>Action</th>
  10. </tr>
  11. </thead>
  12. <?php
  13. require_once('connection.php');
  14. $result = $conn->prepare("SELECT * FROM tbl_registration ORDER BY tbl_registration_id ASC");
  15. $result->execute();
  16. for($i=0; $row = $result->fetch(); $i++){
  17. $id=$row['tbl_registration_id'];
  18. ?>
  19. <tr>
  20. <td><label><?php echo $row['tbl_registration_id']; ?></label></td>
  21. <td><label><?php echo $row['first_name']; ?></label></td>
  22. <td><label><?php echo $row['middle_name']; ?></label></td>
  23. <td><label><?php echo $row['last_name']; ?></label></td>
  24. <td><label><?php echo $row['email']; ?></label></td>
  25. <td><label><?php echo $row['contact_number']; ?></label></td>
  26. <td>
  27. <a href="delete.php<?php echo '?tbl_registration_id='.$id; ?>" class="btn btn-danger">
  28. <button class="btn_delete">
  29. Delete Record
  30. </button>
  31. </a>
  32. </td>
  33. </tr>
  34. <?php } ?>
  35. </tbody>

And, this is our PHP Delete Query to deleting data in our table.

  1. <?php
  2. require_once('connection.php');
  3.  
  4. $get_id=$_GET['tbl_registration_id'];
  5.  
  6. // sql to delete a record
  7. $sql = "Delete from tbl_registration where tbl_registration_id = '$get_id'";
  8.  
  9. // use exec() because no results are returned
  10. $conn->exec($sql);
  11. echo "<script>alert('Successfully Deleted!'); window.location='index.php'</script>";
  12. ?>

And, this is the output after deleting data in the table. The tbl_registration_id = 2 was deleted.

Result

Share us your thoughts and comments below. Thank you so much for dropping by and reading this tutorial post. For more updates, don’t hesitate and feel free to visit this website more often and please share this with your friends or email me at [email protected]. Practice Coding. Thank you very much.


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.