WEB DEVELOPMENT

Prepared-Statement

MySQL Prepared Statements with Bind

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.

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

SELECT


<?php

$sql = "SELECT * FROM tablename WHERE id = ?";		
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $_POST['id']);	
$stmt->execute();

$result = $stmt->get_result();			
while ($product = $result->fetch_assoc()) { 

	echo $product['id'];
	echo $product['name'];

}
?>

INSERT


<?php

$sql = "INSERT INTO tablename (name, quantity, price) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);

$stmt->bind_param("sid", $_POST['name'], $_POST['quantity'], $_POST['price']);

if($stmt->execute()){
	return true;
}

?>

Get the last inserted id after executing the insert prepared statement.


<?php

echo $conn->insert_id;
      
?>

UPDATE


<?php

$sql = "UPDATE tablename SET name = ?, quantity = ?, price = ? WHERE id = ?";
$stmt = $conn->prepare($sql);

$stmt->bind_param("sidi", $_POST['name'], $_POST['quantity'], $_POST['price'], $_POST['id']);

if($stmt->execute()){
	return true;
}

?>

DELETE


<?php

$sql = "DELETE FROM tablename WHERE id = ?";
$stmt = $conn->prepare($sql);

$stmt->bind_param("i", $_POST['id']);

if($stmt->execute()){
	return true;
}

?>