MYSQLI DELETE Prepared Statement

This is how to make a PHP mysqli DELETE query with a prepared statement. This example includes error checking at every step. Error checking will help you debug your prepared mysqli query when it’s failing and you don’t know why. You can use this example to help you debug any mysqli prepared statement, not only a DELETE query.

This example assumes that you want to delete a row in a MySQL table if it has a certain value for a column named column_name. You can change the column name on line 17. On line 33, the variable $value is assumed for the value. It is assumed that this value is a string. If you are using a numerical value, change the s to a d on line 33.

You must also replace table_name on line 17. On line 5, you have to replace localhost, DB_USERNAME, DB_PASSWORD, and DB_NAME with your own database details.

<?php

// Connect to database

$mysqli = new mysqli("localhost", "DB_USERNAME", 'DB_PASSWORD', "DB_NAME");

// Check connection

if ($mysqli->connect_errno) {
    error_log("Connect failed:");
    error_log( print_r( $mysqli->connect_error, true ) );
    exit();
}

// Prepare a DELETE statement

$stmt = $mysqli->prepare("DELETE FROM table_name WHERE column_name = ?");

// Check if prepare() failed.
// prepare() can fail because of syntax errors, missing privileges, ....

if ( false === $stmt ) {
	error_log('mysqli prepare() failed: ');
	error_log( print_r( htmlspecialchars($stmt->error), true ) );

	// Since all the following operations need a valid/ready statement object
  	// it doesn't make sense to go on
  	exit();
}

// Bind the value to the statement

$bind = $stmt->bind_param('s', $value);

// Check if bind_param() failed.
// bind_param() can fail because the number of parameter doesn't match the placeholders
// in the statement, or there's a type conflict, or ....

if ( false === $bind ) {
	error_log('bind_param() failed:');
	error_log( print_r( htmlspecialchars($stmt->error), true ) );
	exit();
}

// Execute the query

$exec = $stmt->execute();

// Check if execute() failed. 
// execute() can fail for various reasons. And may it be as stupid as someone tripping over the network cable

if ( false === $exec ) {
	error_log('mysqli execute() failed: ');
	error_log( print_r( htmlspecialchars($stmt->error), true ) );
}

// Close the prepared statement

$stmt->close();

// Close connection

$mysqli->close();