12g.md

Don't forget to hit the ⭐ if you like this repo.

PHP MySQL Prepared Statements

A prepared statement (also known as a parameterized statement) is simply a SQL query template containing placeholders instead of the actual parameter values. These placeholders will be replaced by the actual values at the time of execution of the statement. Prepared statements also provide strong protection against SQL injection because parameter values are not embedded directly inside the SQL query string. Database: demo1, table: persons.

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo1");
 
// 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 = "Ananda Riza";
    $last_name = "Pratomo";
    $email = "ananda@mail.com";
    mysqli_stmt_execute($stmt);
    
    /* Set the parameters values and execute
    the statement to insert a row */
    $first_name = "Shafia Carina";
    $last_name = "Rachmi";
    $email = "shafia@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);
?>

1. The code starts by defining an SQL query string with placeholders (?) for the values to be inserted. This allows the use of prepared statements, which can improve security and performance by separating the SQL logic from the data.

2. The mysqli_prepare() function is used to create a prepared statement based on the SQL query. It takes two parameters: the connection object ($link) representing the connection to the MySQL server, and the SQL query string ($sql) to be prepared.

3. If the mysqli_prepare() function returns a prepared statement object ($stmt), the code proceeds to bind the variables to the prepared statement using mysqli_stmt_bind_param(). This function binds the variables $first_name, $last_name, and $email to the prepared statement, specifying their data types using the "sss" format string.

4. After binding the variables, the code sets their values and executes the statement using mysqli_stmt_execute(). In this code, the statement is executed twice, inserting two rows of data into the "persons" table.

5. If the execution of the prepared statement is successful, the code inside the if block is executed, printing the message "Records inserted successfully." using the echo statement.

6. If there is an error in preparing the query or executing the prepared statement, the code inside the else block is executed. It uses the mysqli_error() function to retrieve the specific error message from the MySQL server and concatenates it with the error message "ERROR: Could not prepare query: $sql.".

By using prepared statements, you can safely and efficiently insert data into the "persons" table in the MySQL database while protecting against SQL injection attacks.

Contribution 🛠️

Please create an Issue for any improvements, suggestions or errors in the content.

You can also contact me using Linkedin for any other queries or feedback.