In this tutorial, you will learn how you can use the PHP prepared statement with MySQLi POP, OOP, and PDO to insert data into the MySQL database.
The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency and protect against SQL injections.
How to use the PHP MySQL prepared statements
1. PHP Prepared statements with MySQLi procedural.
<?php
// Database connection
$database_connection= mysqli_connect("localhost","root","","database_name");
// Check database connection
if (mysqli_connect_errno()){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$stmt = mysqli_prepare($database_connection, "INSERT INTO `users` (first_name, last_name, email) VALUES (?, ?, ?)");
// Data binding
// "sss" is data type
mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
// Assigning data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';
// if execute the prepared statement
if(mysqli_stmt_execute($stmt)){
echo "Data inserted successfully.";
}
// else data not inserted
else{
echo "Something going wrong!";
}
?>In the mysqli_stmt_bind_param you can see the "sss", these triple s represent the types of data to be inserted.
In our case, we will insert the $first_name, $last_name, and $email and all three are string types data, that’s why we have added three s.
If $first_name is integer and $last_name and $email is string, then it would be "iss".
| Character | Description |
|---|---|
| i | If the corresponding variable type is an integer(Whole number) |
| d | If the corresponding variable type is a double(decimal number) |
| s | string(text) |
| b | blob(Binary Large Object) |
2. Prepared statements with PHP MySQLi object-oriented programming.
<?php
// Database connection
$database_connection = new mysqli("localhost", "root", "", "database_name");
// Check database connection
if ($database_connection === false){
echo "Failed to connect to MySQL: " . $database_connection->connect_error;
}
// Insert query (Insert data into users table)
$stmt = $database_connection->prepare("INSERT INTO `users` (first_name, last_name, email) VALUES (?,?,?)");
$stmt->bind_param("sss", $first_name, $last_name, $email);
// Assigning data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';
if($stmt->execute()){
echo "Data inserted successfully.";
}else{
echo "Something error occurred.";
}
?>3. Prepared statements with PHP Data Objects (PDO).
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database_name = "database_name";
// CREATE DATABASE CONNECTION USING PDO METHOD
try {
$database_connection = new PDO("mysql:host=$servername;dbname=$database_name", $username, $password);
// Set the PDO error mode to exception
$database_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection Failed" . $e->getMessage();
}
// INSERT DATA INTO THE DATABASE
try {
$sql = "INSERT INTO `users` (first_name, last_name, email) VALUES (?,?,?)";
$stmt = $database_connection->prepare($sql);
$stmt->bindParam(1, $first_name, PDO::PARAM_STR);
$stmt->bindParam(2, $last_name, PDO::PARAM_STR);
$stmt->bindParam(3, $email, PDO::PARAM_STR);
// Assigning data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';
$stmt->execute();
echo "Data inserted successfully";
} catch (PDOException $e) {
echo "data not inserted";
}
?>You can use the colon(:) before a place holder text to replace the question marks.
VALUES (:first_name, :last_name, :email)
$stmt->bindParam(':first_name', $first_name, PDO::PARAM_STR);
$stmt->bindParam(':last_name', $last_name, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);