PHP : MySQL Delete Data

To delete data from a MySQL database table using PHP, you can use the DELETE SQL statement. This allows you to remove specific rows from a table based on certain conditions. Below are examples using both mysqli and PDO to delete data from a users table.

Using mysqli:

Here's an example of using mysqli to delete a row from the users table where the id is equal to 1:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// ID to delete
$id = 1;

// SQL query to delete data
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $id); // "i" for integer
$stmt->execute();

if ($stmt->affected_rows > 0) {
    echo "Record deleted successfully";
} else {
    echo "No records deleted";
}

// Close connection
$conn->close();
 

Using PDO:

Here's the same example using PDO:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    // Create connection
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // ID to delete
    $id = 1;

    // SQL query to delete data
    $sql = "DELETE FROM users WHERE id = :id";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->execute();

    if ($stmt->rowCount() > 0) {
        echo "Record deleted successfully";
    } else {
        echo "No records deleted";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
 

Explanation:

• Both examples use an SQL DELETE FROM users WHERE id = ? query to delete a row from the users table where the id matches the specified value ( 1 in this case).
• The WHERE clause is used to specify the condition for deletion.
• In the mysqli example, bind_param("i", $id) is used to bind the parameter $id as an integer.
• In the PDO example, bindParam(:id, $id, PDO::PARAM_INT) is used to bind the parameter $id as an integer.

Important Notes:

• Adjust the $id variable to the value you want to delete.
• The DELETE statement can be used with different conditions for more complex deletions.
• Always verify that the connection is successful before executing queries.
• Close the connection after use to free up resources.

Running these scripts will delete a row from the users table where the id matches the specified value ( 1 ). Adjust the condition in the WHERE clause as needed for your specific query.