I understand the confusion. Let me provide a deeper explanation and an example to prove why to use prepare statement and not query and how they are fast and sql safe.
In traditional SQL queries, you send the complete SQL statement as a text string to the database. This means that every time you execute a query, even if it’s just a slight variation of a previous one, the database needs to parse the entire statement and figure out what to do. This parsing process can be computationally expensive, especially for complex queries or when the queries are executed many times.
Prepared statements change this by separating the SQL statement from the actual data. You first send a template of your SQL statement, with placeholders for the data. We call this preparing the statement or prepared statements. The database parses this statement, compiles it, and keeps it ready for execution. Then, when you’re ready to run the query, you just send the data, which replaces the placeholders (?) in the compiled statement. This can be more efficient because the database only needs to parse and compile the statement once, but it can execute it multiple times with different data.
Let’s have a look at this with an example. Suppose you’re running an application where you frequently need to update user records. With traditional SQL queries, you might do something like this:
for ($i = 1; $i <= 100; $i++) {
$sql = "UPDATE users SET name = 'User$i' WHERE id = $i";
mysqli_query($conn, $sql);
}
In this case, even though the structure of the SQL statement is the same for each loop iteration, the database has to parse each statement individually because the name and id values change each time.
Now, let’s do the same thing with a prepared statement:
$stmt = $mysqli->prepare('UPDATE users SET name = ? WHERE id = ?');
for ($i = 1; $i <= 100; $i++) {
$stmt->bind_param('si', "User$i", $i);
$stmt->execute();
}
$stmt->close();
In this case, we send the SQL statement to the database first, with placeholders (?) for the name and id values. The database can parse this statement, figure out the execution plan, and have it ready to go. Then, in the loop, we just send the actual name and id values. The database already knows what to do, so it just needs to plug in the new values and execute the statement. This can save a lot of computational effort on the database’s part, especially when the same statement needs to be executed many times.
#happytocode #mysql #db #prepared #dba