The examples on this page assumes the use of the class and connection defined at the top of the Selecting Data page.
bindValueThe following example selects a single member from the
member table given a member_id value.
The member_id could come from a web form or some
other source:
public function select( $member_id ) {
$sql = 'SELECT * FROM member WHERE member_id = ?';
$stmt = $this->conn->prepare( $sql );
$stmt->bindValue( 1, $member_id );
$stmt->setFetchMode( PDO::FETCH_ASSOC );
$stmt->execute();
return $stmt->fetchAll();
}
This method is very similar to the selectAll
method example on the Selecting
Data page. The differences lie in the use of the
? placeholder in the SQL statements and in the use
of the bindValue method of the $stmt
object. The line:
$sql = 'SELECT * FROM member WHERE member_id = ?';
uses a ? as a placeholder for the comparison
value for member_id. After this statement is
prepared a value can be given for the placeholder using the
bindValue or bindParam methods. In
this example the binding is done with:
$stmt->bindValue( 1, $member_id );
The bindValue method associates a value with a
placehold in the matching SQL statement. In this case the value
stored in the $member_id variable is associated
with the first placeholder, numbered 1. (Placeholders are
numbered starting with 1.) Thus, if $member_id had
the value 25, this would be equivalent to executing the SQL
statement:
SELECT * FROM member WHERE member_id = 25
The bindValue and bindParam
methods filter and escape whatever values are eventually passed
to the SQL statement. Using these methods eliminates the
possiblity of SQL injection attacks.
SQL statements may have multiple values bound to them, as in the following example:
public function selectRandomQuestions( $quiz, $limit ) {
$sql = 'SELECT qid FROM quizQuestion WHERE quiz = ? ORDER BY rand() LIMIT ?';
$stmt = $this->conn->prepare( $sql );
$i = 1;
$stmt->bindValue( $i++, $quiz );
$stmt->bindValue( $i++, $limit, PDO::PARAM_INT );
$stmt->setFetchMode( PDO::FETCH_ASSOC );
$stmt->execute();
return $stmt->fetchAll();
}
This function's SQL statement selects an arbitrary number
($limit) of questions from a randomized list of
questions. The clause ORDER BY rand() selects the
question in a random order. The LIMIT clause tells
the database the number of rows to actually select. For
example, a LIMIT of 1 would return only 1 row.
This is a simple way of selecting random records from a table
through SQL rather than doing so with the application
language.
In this example the SQL statement:
$sql = 'SELECT qid FROM quizQuestion WHERE quiz = ? ORDER BY rand() LIMIT ?';
has two placeholders, one in its WHERE clause
and the other in its LIMIT clause. The
placeholders are numbered in the order they appear - the first
is 1, the second is 2. The actual binding values are assigned
by the lines:
$i = 1;
$stmt->bindValue( $i++, $quiz );
$stmt->bindValue( $i++, $limit, PDO::PARAM_INT );
The value of the variable $quiz is assigned to
the first placeholder and the value of $limit is
assigned to the second placeholder.
The use of the $i variable and its increment
$i++ in the bindValue calls is
strictly for convenience - this way the bindings done in order
without having to explicitly give the binding numbers.
Note the third parameter, PDO::PARAM_INT, for
the second binding. This explicitly informs the database driver
that the value for $limit must be treated as an
integer. It should be noted that although MySQL is very
flexible towards how it treats values - it normally converts
between strings an integers without problems - it requires that
values used in its LIMIT clause are integers. If
necessary, values can be explicitly converted to integer using
PHP's intval function.
LIKEThe following function shows how to use the %
wildcard with bound values:
public function selectByName( $lname, $fname, $inst ) {
$sql = 'SELECT * FROM member ';
$sql .= 'WHERE last_name LIKE ? AND first_name LIKE ? AND institution LIKE ? ';
$sql .= 'ORDER BY last_name, first_name';
$stmt = $this->conn->prepare( $sql );
$i = 1;
$stmt->bindValue( $i++, "$lname%" );
$stmt->bindValue( $i++, "$first_name%" );
$stmt->bindValue( $i++, "%$inst%" );
$stmt->setFetchMode( PDO::FETCH_ASSOC );
$stmt->execute();
return $stmt->fetchAll();
}
This function selects all DCRIS members whose last names
begin with $lname, first names begin with
$fname, and whose institution name contains
$inst anywhere in the string.
The $sql variable is defined across multiple
lines using string concatenation. The variables
$lname and $fname have the wildcard
character % added as a suffix simply by putting
both the variable and the wildcard character within double
quotes so that the entire term is parsed. The
$inst variable has the wildcard added as both a
prefix and suffix. If any of the three variables are empty then
the wildcards stand alone - thus the user can enter only one or
two of the three values requested and the selection will still
work properly. Note that all three variables could have been
defined as containing the wildcard when initialized. Adding the
wildcard at the binding is simply a style choice, but one that
clearly shows the wildcards being used.
Each placeholder in the SQL string must have a matching bound value. Having more or less numbers of bindings than placeholders throws an exception during execution.
INThe above requirement can be difficult to adhere to if we don't know how
many values are going to be used in a SQL statement, particularly with
the IN clause. Imagine that we have a form that contains a
drop-down list that allows a number of different values to be chosen, as in
this list of expertises:
This HTML select element is named keyword_ids[].
If a user chooses Doctrine, Military History, and
Terrorism, the PHP page that processes this form is passed these values in a
variable $_POST[keyword_ids] that contains the array
{21,12,9}. (Other selections would create different arrays.)
If we wish to use this array with an IN clause, we must construct
an appropriate SQL statement and bind the correct number of values to it.
In the following example, we want to select the DCRIS Members who have all the
expertises selected by the user from the box above. We must use an IN
clause that select refers to a subquery that extracts the Member ID values that
we want:
public function selectMembersByKeywords() {
// Get the array of keyword ID values.
$k_ids = $_POST['keyword_ids'];
// Get the number of ids in the keyword ID array.
$n = count( $k_ids );
// Create a string of question mark placeholders.
$qmarks = rtrim( str_repeat( '?, ', $n ), ', ');
// Define the main SQL statement.
$sql = 'SELECT * FROM member WHERE member_id IN ';
// Define the SQL subquery using the $qmarks placeholder.
$sql .= "(SELECT member_id FROM member_keyword WHERE keyword_id IN ( $qmarks )) ";
// Define the sort order.
$sql .= 'ORDER BY last_name, first_name';
// Prepare the statement.
$stmt = $this->conn->prepare( $sql );
// Bind the keyword ID numbers to the statement.
$i = 1;
foreach( $k_ids AS $k_id ) {
$stmt->bindValue( $i++, $k_id, PDO::PARAM_INT );
}
// Execute the statement and return the values.
$stmt->setFetchMode( PDO::FETCH_ASSOC );
$stmt->execute();
return $stmt->fetchAll();
}
The resulting $sql string would be:
SELECT * FROM member WHERE member_id IN (SELECT member_id FROM member_keyword WHERE keyword_id IN ( ?, ?, ? )) ORDER BY last_name, first_name
and the three placeholding question marks would be bound to 21, 12, and 9 in that order by the value binding loop.
Note that you may bind other values to the SQL statement if it contains other
clauses so long as $i is incremented properly.