Parameterized statement also known as prepared statement. It is simply a SQL query template containing placeholder instead of the actual parameter values. Placeholders will replaced by actual values at the time of execution.
PDO supports anonymous positional placeholder (?) & named placeholders. Named placeholder begins with a colon (:) followed by an identifier. Where, MySQLi supports only positional placeholder (?).
PDO
INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email);
MySQLi
INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?);
mysqli_stmt_bind_param() function bind variables to the placeholders (?). Data types of the bind variables are:
- b — binary (such as image, PDF file, etc.)
- d — double (floating point number)
- i — integer (whole number)
- s — string (text)
MySQLi Procedural
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
/* Set the parameters values and execute
the statement again to insert another row */
$first_name = "Hermione";
$last_name = "Granger";
$email = "hermionegranger@mail.com";
mysqli_stmt_execute($stmt);
/* Set the parameters values and execute
the statement to insert a row */
$first_name = "Ron";
$last_name = "Weasley";
$email = "ronweasley@mail.com";
mysqli_stmt_execute($stmt);
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);
}
// Close statement
mysqli_stmt_close($stmt);
// Close connection
mysqli_close($link);
?>
MySQLi Object Oriented
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("sss", $first_name, $last_name, $email);
/* Set the parameters values and execute
the statement again to insert another row */
$first_name = "Hermione";
$last_name = "Granger";
$email = "hermionegranger@mail.com";
$stmt->execute();
/* Set the parameters values and execute
the statement to insert a row */
$first_name = "Ron";
$last_name = "Weasley";
$email = "ronweasley@mail.com";
$stmt->execute();
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not prepare query: $sql. " . $mysqli->error;
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
?>
PDO
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt insert query execution
try{
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
$stmt = $pdo->prepare($sql);
// Bind parameters to statement
$stmt->bindParam(':first_name', $_REQUEST['first_name'], PDO::PARAM_STR);
$stmt->bindParam(':last_name', $_REQUEST['last_name'], PDO::PARAM_STR);
$stmt->bindParam(':email', $_REQUEST['email'], PDO::PARAM_STR);
// Execute the prepared statement
$stmt->execute();
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not prepare/execute query: $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>