Reading Time: 6 minutes

2,470 Views

Inside this article we will see the concept i.e PHP Insert Data Into 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 insert data into 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;

How To Use MySQLi Prepare Method

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

$stmt = $mysqli->prepare("INSERT INTO items (name, description, price, category_id) VALUES (?, ?, ?, ?)");

$stmt is an instance of prepare mysqli prepare statement.

Here, we have written the simple insert query. In the first bracket we have mention the column names of items table. Secondly we have the placeholders i.e ?

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("ssii", $name, $description, $price, $category_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

“ssii” 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";
$description = "Best and Affordable Machine";
$price = 12000;
$category_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 insert 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("INSERT INTO items (name, description, price, category_id) VALUES (?, ?, ?, ?)");
$stmt->bind_param("ssii", $name, $description, $price, $category_id);

// set parameters and execute
$name = "Toshiba Sewing Machine";
$description = "Best and Affordable Machine";
$price = 12000;
$category_id = 2;

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

//var_dump($stmt);

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

Application Testing

Open application into browser.

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

Output

Item created successfully

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