×
☰ See All Chapters

PHP Prepared statements in MySQLi

A prepared statement is an SQL statement that contains parameters, and the prepare (Object oriented style)/ mysqli_prepare (Procedural style) functions are used to create a prepared SQL statement. Before a prepared statement can be executed, each parameter needs to be assigned using bind_param (Object oriented style) / mysqli_stmt_bind_param  (Procedural style) functions. A question mark is used to denote a parameter. For example, the following prepared statement inserts a new row in a table called Employees:

INSERT INTO Employees VALUES (?, ?, ?, ?)

The ? represents the values we will provide later. Please note that we do not need quotes for the placeholders, regardless of the type. We can also only provide placeholders in the data portions of the query, meaning SET, VALUES and WHERE. You cannot use placeholders in the SELECT or FROM portions.

Prepared statements are executed using execute (Object oriented style) /mysqli_stmt_execute (Procedural style).

Prepared statements are preferred over simple statements for below good reasons:

  • Prepared statements reduce parsing time as the preparation on the query is done only once (although the statement is executed multiple times), so prepared statements execute faster. 

  • Prepared statements are easier to code because you do not have to worry about things like single quotes around text or missing commas. 

  • Prepared statements are safer from SQL injections, because parameters are provided later by specifying the type of parameter and need not be correctly escaped. 

Binding Parameters

  • bind_param (Object oriented style) / mysqli_stmt_bind_param  (Procedural style) are used to bind the parameters. 

  • The first parameter for these functions bind_param// mysqli_stmt_bind_param  should be a string, number of characters in this string should be same as number of parameters in SQL (number of question marks used in SQL ). Each character in the string represents the type of value for each parameter in SQL. Below are the parameter data type of the bound parameter 

i

integer

d

double

s

string

b

BLOB

  • After specifying the data type for parameters subsequent parameters are the values to the parameters of SQL. You should provide values to all the parameters of SQL. In the below example we passed "sid" as first parameter to bind_param which means first value “Manu Manjunatha” is string, second value 100 is integer and third value 100000 is double. 

$stmt = $conn->prepare("INSERT INTO EMPLOYEE (name, emp_id, salary) VALUES (?, ?, ?)");

$stmt->bind_param("sid", $name, $id, $salary);

 

$name = "Manu Manjunatha";

$id = 100;

$salary = 100000;

The values to the sql parameters should not be passed directly to bind_param function. Values should be passed through reference variable. Hence the below call to bind_param function will not work.

$stmt->bind_param("sid", "Manu Manjunatha", 100, 100000);

 

 

PHP Prepared statement procedural style example

DB Script

CREATE TABLE EMP(EMP_ID INT(5), NAME VARCHAR(30), SALARY INT(6));

COMMIT;

index.php

<?php

 

$servername = "localhost";

$username = "root";

$password = "pass";

$dbname = "study";

 

// Create connection

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection

if (!$conn) {

    die("Connection failed: " . mysqli_connect_error());

}

 

$stmt = mysqli_prepare($conn, "INSERT INTO EMP (name, emp_id, salary) VALUES (?, ?, ?)");

 

mysqli_stmt_bind_param($stmt, "sid", $name, $id, $salary);

 

$name = "Manu Manjunatha";

$id = 100;

$salary = 100000;

 

$result = mysqli_stmt_execute($stmt);

 

if ($result ==  true) {

    echo "</br>New record created Successfully";

}

 

mysqli_stmt_close($stmt);

?>

PHP Prepared statement object oriented style example

DB Script

CREATE TABLE EMP(EMP_ID INT(5), NAME VARCHAR(30), SALARY INT(6));

COMMIT;

index.php

<?php

 

$servername = "localhost";

$username = "root";

$password = "pass";

$dbname = "study";

 

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

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

}

 

$stmt = $conn->prepare("INSERT INTO EMP (name, emp_id, salary) VALUES (?, ?, ?)");

$stmt->bind_param("sid", $name, $id, $salary);

 

$name = "Manu Manjunatha";

$id = 100;

$salary = 100000;

$result = $stmt->execute();

 

if ($result ==  true) {

    echo "</br>New record created Successfully";

}

 

$stmt->close();

?>

 

All Chapters
Author