New Page  |  Edit Page

Prepared Statements

A Prepared Statement (parameterized statement) is created with a query template using placeholders (?) instead of providing actual values. The placeholders are later replaced with actual values that are bound to the parameters in the statement during execution.

PHP 8.0

SELECT


  $stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");
  $stmt->execute([$email]);
Select Data via PHP 8.0 Shorthand

INSERT


  $stmt = $mysqli->prepare("INSERT INTO users (email, password) VALUES (?,?)");
  $stmt->execute([$email, $password_hash]);
Insert Data via PHP 8.0 Shorthand

UPDATE


  $stmt = $mysqli->prepare("UPDATE users SET name=?, email=?, password=? WHERE id=?");
  $stmt->execute([$name, $email, $password, $id]);
Upate Data PHP 8.0 Shorthand

DELETE


  $stmt = $mysqli->prepare("DELETE FROM users WHERE id=?");
  $stmt->execute([$id]);
Delete Data via PHP 8.0 Shorthand

PHP 7.X

Binding Parameters

A prepared statement also provides protection against SQL injection as the query is created with placeholders that are replaced with input values later during 'bind param' and 'execute'. There is also no need of escaping input values as the values are treated as literals avoiding the threat an SQL injection.

There are four types of binding allowed regarding variables:
"i" - Integer, "d" - Doubles, "b" - Blob, "s" - String

INSERT


$sql = "SELECT * FROM tablename WHERE id = ?";		
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("i", $_POST['id']);	
$stmt->execute();
Select Data via Prepared Statment

UPDATE


$sql = "UPDATE tablename SET name = ?, quantity = ?, price = ? WHERE id = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("sidi", $_POST['name'], $_POST['quantity'], $_POST['price'], $_POST['id']);
if($stmt->execute()){
	 return true;
}
Update Data via Prepared Statement

INSERT


$sql = "INSERT INTO tablename (name, quantity, price) VALUES (?, ?, ?)"
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("sid", $_POST['name'], $_POST['quantity'], $_POST['price']);
if($stmt->execute()){
	 return true;
}
Insert Data via Prepared Statement

DELETE


$sql = "DELETE FROM tablename WHERE id = ?";
$stmt = $mysqli->prepare($sql);
    
$stmt->bind_param("i", $_POST['id']);
    
if($stmt->execute()){
    return true;
}
Delete Data via Prepared Statement