PHP Tutorial

MySQL Commands-Prepared Statement

When you want to execute the same statement or similar statements multiple times with high efficiency you can used prepared statement and it is very useful against SQL injections. Prepare statement creates a template and send it to database. Parameters are left unspecified with ?. For example, INSERT INTO Visitors(?,?).

The database stores the result without executing it after parsing and compiling. You can then bind the values to the unspecified parameters and execute the database statement. Prepared statements has below three advantages.

1. It reduces the parsing time as the preparation is done only once for the statement.

2. Bound parameters helps to reduce bandwidth to the server as you need not send the whole query every time.

3. They are useful against SQL injections as the parameter values need to be correctly escaped and the template is not derived using external input.

Let us see example of Prepared Statement using MySQLi

?php
$server-name = "localhost";
$username = "username";
$password = "password";
$db_name = "my_project";

$conn = new mysqli($server_name, $username, $password, $db_name);

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

// prepare and bind the values
$statement = $conn->prepare("INSERT INTO Visitors(name, email) VALUES (?, ?, ?)");
$statement ->bind_param("ss", $name, $email);

// set parameters and execute
$name = "Ben";
$email = "ben@example.com";
$statement ->execute();

$name = "Paul";
$email = "paul@example.com";
$statement ->execute();
echo "New records created successfully";
$statement->close();
$conn->close();
?>

In the above php code while binding parameter “ss” lists the type of data that parameters are, and the arguments can be of below types.

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

PHP Tutorial

PHP Introduction

PHP Starters

PHP Forms

PHP Advanced

MySQL Database

PHP OOP

PHP XML

PHP AJAX

PHP Functions

MySQL Commands-Prepared Statement

Popular Exams

Company

Resources