English
English
A prepared statement is a way of executing SQL queries. It divides the SQL query into two parts:
Preparation phase: The SQL template is sent to the database, where it is parsed and compiled but not executed yet. At this point, the variable parts of the query (such as user input) are not inserted into the SQL directly, but represented by placeholders (commonly ?).
Execution phase: During execution, actual parameter values are bound to the placeholders, and then the database executes the statement.
The benefit of this method is that the database can prepare the query in advance and securely validate each parameter, which helps prevent SQL injection attacks.
In PHP, we use the mysqli extension to work with MySQL databases. The mysqli_stmt::__construct function is a constructor of the mysqli_stmt class used to create a prepared statement.
Step overview:
Establish a database connection: First, create a connection using mysqli_connect or new mysqli().
Prepare the SQL statement: Define an SQL query with placeholders.
Initialize the prepared statement: Use mysqli_prepare or mysqli_stmt::__construct to create a prepared statement.
Bind parameters: Use the bind_param method to bind variable values to the placeholders.
Execute the statement: Use the execute method to run the prepared statement.
Suppose we want to insert user data (name, email, and age) into a user table using mysqli_stmt::__construct. Below is a concrete example:
<span><span><span class="hljs-meta"><?php</span></span><span>
</span><span><span class="hljs-comment">// 1. Create a database connection</span></span><span>
</span><span><span class="hljs-variable">$servername</span></span><span> = </span><span><span class="hljs-string">"localhost"</span></span><span>;
</span><span><span class="hljs-variable">$username</span></span><span> = </span><span><span class="hljs-string">"root"</span></span><span>;
</span><span><span class="hljs-variable">$password</span></span><span> = </span><span><span class="hljs-string">""</span></span><span>;
</span><span><span class="hljs-variable">$dbname</span></span><span> = </span><span><span class="hljs-string">"test"</span></span><span>;
<p></span>$conn = new mysqli($servername, $username, $password, $dbname);</p>
<p>// Check connection<br>
if ($conn->connect_error) {<br>
die("Connection failed: " . $conn->connect_error);<br>
}</p>
<p>// 2. Define SQL with placeholders<br>
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";</p>
<p>// 3. Create a prepared statement<br>
$stmt = $conn->prepare($sql);</p>
<p>// Check if preparation was successful<br>
if ($stmt === </span>false) {<br>
</span>die(</span>'Error preparing statement: ' . </span>$conn->error);<br>
}</p>
<p></span>// 4. Bind parameters<br>
$name = </span>"John Doe";<br>
</span>$email = </span>"<a class="decorated-link cursor-pointer" rel="noopener">[email protected]<svg width="20" height="20" viewBox="0 0 20 20" fill="currentColor" xmlns="http://www.w3.org/2000/svg" data-rtl-flip="" class="block h-[0.75em] w-[0.75em] stroke-current stroke-[0.75]"><path d="M14.3349 13.3301V6.60645L5.47065 15.4707C5.21095 15.7304 4.78895 15.7304 4.52925 15.4707C4.26955 15.211 4.26955 14.789 4.52925 14.5293L13.3935 5.66504H6.66011C6.29284 5.66504 5.99507 5.36727 5.99507 5C5.99507 4.63273 6.29284 4.33496 6.66011 4.33496H14.9999L15.1337 4.34863C15.4369 4.41057 15.665 4.67857 15.665 5V13.3301C15.6649 13.6973 15.3672 13.9951 14.9999 13.9951C14.6327 13.9951 14.335 13.6973 14.3349 13.3301Z"></path></svg></a>";<br>
</span>$age = </span>25;</p>
<p></span>// "ssi" indicates the variable types: string, string, integer<br>
$stmt-></span>bind_param</span>(</span>"ssi"</span>, </span>$name</span>, </span>$email</span>, </span>$age</span>);</p>
<p></span>// 5. Execute prepared statement<br>
if (</span>$stmt</span>-></span>execute</span>()) {<br>
</span>echo </span>"New record created successfully"</span>;<br>
} </span>else {<br>
</span>echo </span>"Error: "</span> . </span>$stmt</span>->error;<br>
}</p>
<p></span>// Close statement and connection<br>
$stmt-></span>close</span>();<br>
</span>$conn-></span>close</span>();<br>
</span>?></span><br>
</span>Create a database connection: Connect using new mysqli(). If the connection fails, the program stops and displays an error.
SQL statement: Define an SQL query with placeholders: INSERT INTO users (name, email, age) VALUES (?, ?, ?). The ? placeholders are replaced with data later.
Prepare the statement: Use $conn->prepare($sql) to create a prepared statement. If preparation fails, it outputs an error.
Bind parameters: $stmt->bind_param("ssi", $name, $email, $age) binds the variables to the placeholders. "ssi" specifies parameter types: s for string and i for integer.
Execute the statement: Use $stmt->execute() to run the query. If successful, it shows a success message; otherwise, it prints the error.
Close resources: After execution, close the statement and the database connection to free resources.
Prevents SQL injection: Using placeholders instead of directly concatenating SQL strings effectively prevents malicious users from injecting harmful SQL code.
Better performance: If you need to execute the same query multiple times (such as batch inserts), prepared statements are more efficient because the SQL doesn’t need to be re-parsed every time.
Cleaner code: Prepared statements make code easier to read and maintain, especially when dealing with user input and repeated queries, reducing the risk of SQL concatenation errors.
Related Tags:
mysqli_stmt
