Prepared statements are SQL statements that are only prepared once and then executed as many times as needed with different parameters. The statement and the data are sent separately, helping prevent SQL injection vulnerabilities. Their advantages and their possible uses may vary depending on the database driver you use.
Prepared statements increase performance by checking that the query is valid only the first time it is received.
If valid, the query will then be prepared so that it can be executed as fast as possible the subsequent times it is called.
However, since the statement and the data are sent separately (which is a good and safe practice),
this may create an overhead that will result in worse performance than sending queries directly.
You can only be sure a prepared statement will increase performance by benchmarking it in the context of your application.
However, you can be pretty sure a prepared statement will perform better if you have a thousand of the same INSERT query to execute in your script, for example.
The database class of your driver allows you to create a prepared statement.
It will return an instance of weeDatabaseStatement
that you can use to execute the statement as many times as you need.
To obtain a prepared statement object and execute it, use the following:
<?php $oStatement = weeApp()->db->prepare('INSERT INTO table VALUES(1, 2, 3)'); $oStatement->execute();
The statement object works pretty much like the database object itself. It contains all the methods you need to interact with the prepared statement.
To execute a prepared statement, you need to call the execute method.
The return value obey the same rules as the weeDatabase::query method:
a weeDatabaseResult result object will be returned if the prepared statement is a SELECT query.
<?php // This prepared statement is a SELECT query. $oStatement = $oDb->prepare('SELECT 42 AS answer'); $oResults = $oStatement->execute(); // This prepared statement won't return anything. $oStatement = $oDb->prepare('DELETE FROM answers WHERE value <> 42'); $oStatement->execute();
As usual, you can chain the prepare and execute methods if needed.
<?php $oResults = $oDb->prepare('SELECT 42 AS answer')->execute();
If the query contains parameters, their values must be bound to the prepared statement through its bind method.
You should note that prepared statements only work with named parameters, as opposed with standard queries.
All database drivers use named parameters.
<?php // $oResults is the same result set in the following code snippets. // Calling weeDatabase::query directly. $oResults = $oDb->query('SELECT COUNT(*) FROM fails WHERE type = :type', array('type' => 'epic')); // Using an intermediate prepared statement. $oStatement = $oDb->prepare('SELECT COUNT(*) FROM fails WHERE type = :type'); $oStatement->bind(array('type' => 'epic')); $oResults = $oStatement->execute();
Of course the execution of these two snippets is different, even if the result is the same, as was explained earlier.
The most common use of prepared statement is to create a query and then reuse it multiple times throughout your script.
This is most useful in the case of INSERT or UPDATE queries.
<?php $oStatement = $oDb->prepare('UPDATE news SET category = :new WHERE category = :old'); $oStatement->bind(array('old' => 'GAIM', 'new' => 'Pidgin')); $oStatement->execute(); printf('%d news were moved from "GAIM" to "Pidgin".', $oStatement->numAffectedRows()); // You can also chain these methods if you wish to $oStatement->bind(array('old' => 'development', 'new' => 'dev'))->execute(); printf('%d news were moved from "development" to "dev".', $oStatement->numAffectedRows());
When the prepared statement is an UPDATE or a DELETE query,
you can use the method numAffectedRows of the statement object
to retrieve the number of rows affected by the query like you would do with the database object.
<?php $oStatement = $oDb->prepare("DELETE FROM users WHERE approved = 0"); $oStatement->execute(); printf("%d users were deleted.", $oStatement->numAffectedRows());