PHP MySQL Prepared Statements

PHP MySQL Prepared Statements

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);
?>
Post a Comment