Reading Time: 6 minutes

2,050 Views

Inside this article we will see the concept i.e PHP Update Data of 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 update data to database 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 update operation.

How To Use MySQLi Prepare Method

We have few simple steps which needs to be followed to update data of 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 update data and also we will create an instance of that.

$stmt = $mysqli->prepare("UPDATE items SET name=?, price=? WHERE id=?");

$stmt is an instance of prepare mysqli prepare statement.

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

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

Step #3 – Bind Value with Placeholder

Use $stmt object to bind values.

$stmt->bind_param("sii", $name, $price, $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

“sii” is the data type of bind values. “s” indicates the string value whereas “i” indicates integer value.

// set parameters and execute
$name = "Toshiba Sewing Machine";
$price = 10000;
$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 update data into 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("UPDATE items SET name=?, price=? WHERE id=?");

$stmt->bind_param("sii", $name, $price, $item_id);

// set parameters and execute
$name = "Toshiba Sewing Machine";
$price = 10000;
$item_id = 2;

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

//var_dump($stmt);

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

Application Testing

Open application into browser.

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

Output

Item updated successfully

We hope this article helped you to learn PHP How To Update Data of 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.