PHP MySQL: Delete Data

PHP MySQL: Delete Data

PHP MySQL: Delete Data

Deleting data in MySQL using PHP involves executing an SQL DELETE statement. This allows you to remove one or more rows from a specified table based on a condition.


Steps to Delete Data

  1. Connect to the MySQL Database
  2. Write the SQL DELETE Query
  3. Execute the Query
  4. Check for Success
  5. Close the Connection

Syntax for SQL DELETE

DELETE FROM table_name WHERE condition;
  • table_name: The name of the table where data will be deleted.
  • condition: Specifies which rows to delete. Always include a WHERE clause to avoid deleting all rows.

Code Example: Delete Data

1. Establish a Database Connection

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "example_db"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } ?>

2. Execute an SQL DELETE Query

Delete a user based on their ID:

<?php $sql = "DELETE FROM users WHERE id = 1"; if ($conn->query($sql) === TRUE) { echo "Record deleted successfully"; } else { echo "Error deleting record: " . $conn->error; } ?>

Using Prepared Statements for Security

Prepared statements are recommended to avoid SQL injection, especially for dynamic inputs.

<?php $stmt = $conn->prepare("DELETE FROM users WHERE id = ?"); $stmt->bind_param("i", $id); // Set the ID to delete and execute $id = 2; if ($stmt->execute()) { echo "Record deleted successfully"; } else { echo "Error: " . $stmt->error; } // Close statement and connection $stmt->close(); $conn->close(); ?>

Delete Multiple Records

You can delete multiple rows that match specific conditions:

<?php $sql = "DELETE FROM users WHERE age > 30"; if ($conn->query($sql) === TRUE) { echo "Records deleted successfully"; } else { echo "Error deleting records: " . $conn->error; } ?>

Delete All Rows

To delete all rows in a table without removing the table itself, omit the WHERE clause:

<?php $sql = "DELETE FROM users"; if ($conn->query($sql) === TRUE) { echo "All records deleted successfully"; } else { echo "Error deleting records: " . $conn->error; } ?>

Warning: Without a WHERE clause, all rows in the table will be deleted. Use with caution.

Complete Example

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "example_db"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Delete query $sql = "DELETE FROM users WHERE id = 3"; if ($conn->query($sql) === TRUE) { echo "Record deleted successfully"; } else { echo "Error deleting record: " . $conn->error; } // Close connection $conn->close(); ?>

Key Notes

  1. Always Use WHERE: Avoid accidental deletion of all rows by including a specific WHERE clause.
  2. Use Prepared Statements: Protect against SQL injection when working with dynamic inputs.
  3. Backup Data: Always back up your database before running delete queries.
  4. Check Affected Rows: Use $conn->affected_rows or $stmt->affected_rows to verify the number of rows deleted.

Check Number of Affected Rows

<?php $sql = "DELETE FROM users WHERE age < 20"; $conn->query($sql); if ($conn->affected_rows > 0) { echo $conn->affected_rows . " record(s) deleted."; } else { echo "No records deleted."; } ?>

Let me know if you need more advanced examples or help with error handling!

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close