Trusted answers to developer questions

What are prepared statements in PHP?

Get Started With Machine Learning

Learn the fundamentals of Machine Learning with this free course. Future-proof your career by adding ML skills to your toolkit — or prepare to land a job in AI or Data Science.

Importance of prepared statements

Whenever a query is parsed with an already prepared statement, the time required to do this is reduced because it skips the preparation stage and goes straight into the execution stage. This can happen more than once.
SQL injection attacks are reduced with prepared statements even when data is improperly validated or when cleaned form data is present. This is because the input templateWhich defines the data type to be accepted is stated in the query. Therefore, it is internal rather than external.
When such codes are executed, the whole query is sent only at the first instance. On subsequent execution, only the bound parameters are sent. This goes a long way in conserving the bandwidth of the server.

How PHP MySQLi prepared statements work

1. Preparation

First, there is an initial preparation of the SQL template.
Second, we initialize the preparation using the mysqli_stmt_init() function which passes an argument to the database connection.

2. Parsing and compiling

Using the mysqli_stmt_prepare() function, the database connection is passed as the first argument.
As the second argument, the SQL query is passed and the statement is parsed, compiled, and optimized without execution by the database.

3. Execution

Whenever the values are provided, they are bound to the template variables and the database executes the command.
This is achieved when the mysqli_stmt_bind_param() and the mysqli_stmt_execute() functions pass the relevant parameters.

4. Getting results and closing our statement

The result of the execution can be grabbed using the mysqli_stmt_get_result() function to pass the initialization variable as an argument.
We can close it all using the mysqli_stmt_get_result() function with the initialization variable as an argument.

Examples

In our examples, we have created:

A database called myDatabase with ＄dbcon as the connection variable.
A table called myTable, which has columns with the names email and username from which we would make a selection of rows.
Your table should have some dummy data inside it as well.

With that setup we can get started.

 <?php
$nameOfServer = "localhost";
$serverUsername = "username";
$password = "password";
$databaseName = "myDatabase";
// Create connection
$dbcon = mysqli_connect($nameOfServer, $serverUsername, $password, $databaseName);
// Check connection
if (!$dbcon) {
  die("Connection failed: " . mysqli_connect_error());
}
 //prepare sql template using "?" as placeholders.
 $query = "SELECT * FROM myTable WHERE username = ? OR email = ?; ";
// initialize database connection.    
    $bindstmt = mysqli_stmt_init($dbcon);
//prepare the query and check if successful
    if(!mysqli_stmt_prepare($bindstmt,$query)){
      echo "stmt failed";
      exit();
    }
//Now bin the parameters if preparation was a success.    
    mysqli_stmt_bind_param($bindstmt,'ss',$username, $email);
//execute the statement.    
    mysqli_stmt_execute($bindstmt);
//get the result and fetch it as an array    
    $stmtresult = mysqli_stmt_get_result($bindstmt);
    if($row = mysqli_fetch_assoc($stmtresult)){
    return $row;
    }
    else{
      $result = false;
      return $result;
    }
//Now close the statement and db connection    
   mysqli_stmt_close($bindstmt); 
   mysqli_close($dbcon);
}
?>

<?php
$nameOfServer = "localhost";
$serverUsername = "username";
$password = "password";
$databaseName = "myDatabase";
// Create connection
$dbcon = new mysqli($nameOfServer, $serverUsername, $password, $databaseName);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// prepare and bind
$stmt = $conn->prepare("INSERT INTO myTable (fullname, username, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $fullname, $username, $email);
// set parameters and execute
$fullname = "mary john";
$username = "marrywise";
$email = "mary@wise.com";
$stmt->execute();
//this will insert the values into our table
/*if the value to be inserted was from a form 
you would have to replace our static values here 
with the values of the form input if it is set .
*/
$stmt->close();
$dbcon->close();
;
?>

RELATED TAGS

php

mysqli

CONTRIBUTOR

NDUKWE CHIDERA K.

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)

Learn in-demand tech skills in half the time

PRODUCTS

Mock Interview

New

Courses

Skill Paths

Projects

Assessments