Easy To Learn MySQL Prepared Statement Tutorial in PHP

MySQL Prepared Statement

A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. A prepared statement (also known as parameterized statement) is simply a SQL query template containing placeholder instead of the actual parameter values.Â

Prepared statements basically work like this :

Prepare : Â An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?"). Example : INSERT INTOÂ student_details VALUES(?, ?, ?)

Execute : The execute the parameter values are sent to the server. The server creates a statement from the statement template and these values to execute it.

The Prepared statements is particularly in situations when you execute a particular statement multiple times with different values, for example, a series of INSERT statements.Â

Prepared statements reduces parsing time as the preparation on the query is done only once (although the statement is executed multiple times)

Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

<!DOCTYPE html>
<html>
<head>
	<title>MySQL Prepared Statement</title>
</head>
<body>

<?php
	
	// Create connection
	$conn = new mysqli("localhost", "root", "", "ftl_db");
	
	// Check connection
	if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	}
	
	// prepare and bind
	$stmt = $conn->prepare("INSERT INTO student_details_2 (stu_name, college, email, mobile) VALUES (?, ?, ?, ?)");
	$stmt->bind_param("ssss", $stu_name, $college, $email, $mobile);
	
	// set parameters and execute
	$stu_name = "Raja";
	$college = "Venkateswara";
	$email = "sample@example.com";
	$mobile = "9966463846";
	$stmt->execute();
	
	echo "New record has successfully created..! ";
	// Close statement
	$stmt->close();
	// Close statement
	$conn->close();
	
?>



</body>
</html>

INSERT INTO student_details_2 (stu_name, college, email, mobile) VALUES (?, ?, ?);

In our SQL, we insert a question mark (?) where we want to substitute in an integer, string, double or blob value.

Then, have a look at the bind_param() function:

$stmt->bind_param("sss", $firstname, $lastname, $email);

This function binds the parameters to the SQL query and tells the database what the parameters are. The "sss" argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.

The argument may be one of four types :

i : integer
d : double
s : string
b : BLOB

We must have one of these for each parameter.

By telling mysql what type of data to expect, we minimize the risk of SQL injections.

PHP Tutorial