To prepare and execute an SQL statement that includes variable
input, use the PDO::prepare, PDOStatement::bindParam,
and PDOStatement::execute methods. Preparing a
statement improves performance because the database server creates
an optimized access plan for data retrieval that it can reuse if the
statement is executed again.
Procedure
To prepare and execute an SQL statement that includes
parameter markers:
- Call the PDO::prepare method, passing
the listed arguments:
- statement
- A string that contains the SQL statement, including question marks
(?) or named variables (
:name
) as parameter markers
for any column or predicate values that require variable input. You
can only use parameter markers as a place holder for column or predicate
values. The SQL compiler is unable to create an access plan for a
statement that uses parameter markers in place of column names, table
names, or other SQL identifiers. You cannot use both question mark
(?) parameter markers and named parameter markers (:name
)
in the same SQL statement.
- driver_options
- Optional: An array that contains statement options:
- PDO::ATTR_CURSOR
- This option sets the type of cursor that PDO returns for result
sets. By default, PDO returns a forward-only cursor (PDO::CURSOR_FWDONLY),
which returns the next row in a result set for every call to
PDOStatement::fetch()
.
You can set this parameter to PDO::CURSOR_SCROLL to request a scrollable
cursor.
If the function call succeeds, it returns a PDOStatement
object that you can use in subsequent method calls that are related
to this query.
If the function call fails (returns False),
you can use the PDO::errorCode or PDO::errorInfo method
to retrieve diagnostic information about the error.
- Optional: For each parameter marker in the
SQL string, call the PDOStatement::bindParam method,
passing the listed arguments. Binding input values to parameter markers
ensures that each input value is treated as a single parameter, which
prevents SQL injection attacks against your application.
- parameter
- A parameter identifier. For question mark parameter markers (?),
this is an integer that represents the 1-indexed position of the parameter
in the SQL statement. For named parameter markers (
:name
),
this is a string that represents the parameter name.
- variable
- The value to use in place of the parameter marker
- Call the PDOStatement::execute method,
optionally passing an array that contains the values to use in place
of the parameter markers, either in order for question mark parameter
markers, or as a
:name => value
associative array
for named parameter markers.
Example
Prepare and execute a statement that includes variable
input.
$sql = "SELECT firstnme, lastname FROM employee WHERE bonus > ? AND bonus < ?";
$stmt = $conn->prepare($sql);
if (!$stmt) {
// Handle errors
}
// Explicitly bind parameters
$stmt->bindParam(1, $_POST['lower']);
$stmt->bindParam(2, $_POST['upper']);
$stmt->execute($stmt);
// Invoke statement again using dynamically bound parameters
$stmt->execute($stmt, array($_POST['lower'], $_POST['upper']));
What to do next
If the SQL statement returns one or more result sets,
you can begin fetching rows from the statement resource by calling
the PDOStatement::fetch or PDOStatement::fetchAll method.