English
English
In PHP, the MySQLi extension allows us to connect to a database and execute queries. To enhance security and prevent SQL injection, MySQLi provides prepared statements. In this article, we will introduce how to use the mysqli::stmt_init function to prepare and execute an SQL prepared statement.
A prepared statement is a method of database interaction that separates SQL queries from data. First, a template of the SQL query is sent to the database, then parameters are bound to the query. This method helps prevent SQL injection attacks and improves security.
mysqli::stmt_init is a method in the MySQLi class used to initialize a new prepared statement. It returns a mysqli_stmt object that can be used to bind parameters and execute SQL queries.
Here are the steps to prepare and execute an SQL prepared statement using the mysqli::stmt_init function:
First, we need to connect to the MySQL database. You can use the mysqli class to create a database connection.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";
<p>// Create connection<br>
$conn = new mysqli($servername, $username, $password, $dbname);</p>
<p>// Check connection<br>
if ($conn->connect_error) {<br>
die("Connection failed: " . $conn->connect_error);<br>
}<br>
?><br>
Use the mysqli::stmt_init function to initialize a new prepared statement object. This object will be used to bind parameters and execute SQL queries.
<?php
// Initialize the prepared statement
$stmt = $conn->stmt_init();
<p>// Check if initialization is successful<br>
if (!$stmt) {<br>
die("Prepared statement initialization failed: " . $conn->error);<br>
}<br>
?><br>
Use the prepare method to prepare the SQL query. Suppose we want to perform a simple SELECT query and retrieve user information from the database.
<?php
$sql = "SELECT * FROM users WHERE email = ?";
$stmt->prepare($sql);
?>
Use the bind_param method to bind variables to placeholders (?) in the SQL query. In this example, we are binding a string-type parameter (the user's email).
<?php
$email = "user@example.com";
$stmt->bind_param("s", $email); // "s" indicates a string type
?>
Use the execute method to execute the SQL query.
<?php
$stmt->execute();
?>
Once the query has been executed successfully, you can use the get_result method to fetch the result set, which returns a mysqli_result object.
<?php
$result = $stmt->get_result();
<p>// Fetch and display results<br>
while ($row = $result->fetch_assoc()) {<br>
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . "<br>";<br>
}<br>
?><br>
Once the query is complete, be sure to close both the prepared statement and the database connection.
<?php
$stmt->close();
$conn->close();
?>
By following these steps, you can successfully use the mysqli::stmt_init function to prepare and execute an SQL prepared statement. Prepared statements not only improve the security of your code by preventing SQL injection attacks, but they also enhance the efficiency of database queries, especially when executing the same query multiple times.
$url = "https://api.m66.net/data"; // Replace domain with m66.net
