Reading Time: 6 minutes

1,775 Views

Inside this article we will see the concept i.e PHP Delete Data From Table Using MySQLi Prepare Method. PHP Prepare function is one of the most secure function which binds value with placeholder in a secure way after sanitizing value.

Prepared statements are very useful against SQL injections. It is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency.

Prepare function of mysqli is used to perform each operation with the database like of Insert, Update, Delete, Read, etc.

If you are looking to find a solution to delete data from database table using PHP MySQLi using prepare statement then this article will help you.

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Next,

Table: items

CREATE TABLE `items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(120) NOT NULL,
 `description` text NOT NULL,
 `price` int(5) NOT NULL,
 `category_id` int(5) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Also, consider few rows inside it so that we can see delete operation.

How To Use MySQLi Prepare Method

We have few simple steps which needs to be followed to delete data from database table.

Step #1 – Database Connection

Create an instance of database.

$servername = "localhost";
$username = "admin";
$password = "Admin@123";
$dbname = "php_applications";

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

Here, $mysqli is an object of database connection.

To check database connection i.e successfully connected or not –

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

Step #2 – Create Prepare Method

Next,

We need to use prepare method to delete data and also we will create an instance of that.

$stmt = $mysqli->prepare("DELETE FROM items WHERE id=?");

$stmt is an instance of prepare mysqli prepare statement.

Here, we have written the simple delete query. Also we have the placeholder i.e ? (question mark)

To bind values with the placeholder i.e with the question mark.

Step #3 – Bind Value with Placeholder

Use $stmt object to bind values.

$stmt->bind_param("i", $item_id);

This function binds the parameters to the SQL query and tells the database what the parameters are. The argument may be one of four types:

i – integer
d – double
s – string
b – BLOB

“i” indicates integer value.

// set parameter and execute
$item_id = 2;

Step #4 – Execute Statement

Finally we need to execute the statement. Again we need to use $stmt object of mysqli prepare statement.

$stmt->execute()

Complete Code – MySQLi Prepare Method

Here, is the complete code which delete data from database table.

<?php
$servername = "localhost";
$username = "admin";
$password = "Admin@123";
$dbname = "php_applications";

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

// Check connection
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

// prepare and bind
$stmt = $mysqli->prepare("DELETE FROM items WHERE id=?");

$stmt->bind_param("i", $item_id);

// set parameter and execute
$item_id = 2;

if ($stmt->execute()) {
    echo "Item deleted successfully";
} else {
    echo "Failed to delete Item";
}

//var_dump($stmt);

$stmt->close();
$mysqli->close();

Application Testing

Open application into browser.

URL: http://localhost/mysqli-prepare/delete.php

Output

Item deleted successfully

We hope this article helped you to learn PHP How To Delete Data From Table Using Prepare Method Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.