PDO->lastInsertId()
Using prepared SELECT statements on a MySQL database prior to MySQL 5.1.17 can lead to SERIOUS performance degradation.
Quote from http://dev.mysql.com/doc/refman/5.1/en/query-cache.html :
>> The query cache is not used for server-side prepared statements before MySQL 5.1.17 <<
The MySQL query cache buffers complete query results and is used to satisfy repeated identical queries if the underlying tables do not change in the meantime - just what happens all the time in a typical web application. It speeds up queries by a several hundred to a several thousand percent.
Obviously, it doesn't make much sense to give up query caching for the relatively small performance benefit of prepared statements (i.e. the DBMS not having to parse and optimize the same query multiple times) - so using PDO->query() for SELECT statements is probably the better choice i you're connecting to MySQL < 5.1.17.
PDO->prepare()
(PHP 5 >= 5.1.0, PECL pdo:0.1-1.0.3)
PDO->prepare() — Prepares a statement for execution and returns a statement object
Description
PDO
PDOStatement prepare
( string $statement
[, array $driver_options
] )
Prepares an SQL statement to be executed by the PDOStatement->execute() method. The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. You cannot use both named and question mark parameter markers within the same SQL statement; pick one or the other parameter style.
You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement->execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.
Calling PDO->prepare() and PDOStatement->execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.
PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other.
Parameters
- statement
-
This must be a valid SQL statement for the target database server.
- driver_options
-
This array holds one or more key=>value pairs to set attribute values for the PDOStatement object that this method returns. You would most commonly use this to set the PDO::ATTR_CURSOR value to PDO::CURSOR_SCROLL to request a scrollable cursor. Some drivers have driver specific options that may be set at prepare-time.
Return Values
If the database server successfully prepares the statement, PDO->prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO->prepare() returns FALSE.
Examples
Example#1 Prepare an SQL statement with named parameters
<?php
/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();
$sth->execute(array('calories' => 175, 'colour' => 'yellow'));
$yellow = $sth->fetchAll();
?>
Example#2 Prepare an SQL statement with question mark parameters
<?php
/* Execute a prepared statement by passing an array of values */
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->execute(array(150, 'red'));
$red = $sth->fetchAll();
$sth->execute(array(175, 'yellow'));
$yellow = $sth->fetchAll();
?>
add a note
User Contributed NotesPDO->prepare()
Stan
14-Nov-2007 10:35
14-Nov-2007 10:35
www.onphp5.com
07-Apr-2007 09:41
07-Apr-2007 09:41
Please note that the statement regarding driver_options is misleading:
"This array holds one or more key=>value pairs to set attribute values for the PDOStatement object that this method returns. You would most commonly use this to set the PDO::ATTR_CURSOR value to PDO::CURSOR_SCROLL to request a scrollable cursor. Some drivers have driver specific options that may be set at prepare-time"
From this you might think that scrollable cursors work for all databases, but they don't! Check out this bug report:
http://bugs.php.net/bug.php?id=34625
Greg MacLellan
23-Feb-2007 01:51
23-Feb-2007 01:51
A more versatile version of the below, but supporting an associative array and arbitrary number of fields:
<?php
public function matchCriteria($fields=null) {
$db=DB::conn();
$sql=array();
$paramArray=array();
if(is_array($fields)) {
foreach ($fields as $field=>$value)) {
$sql[] = $field.'=?';
$paramArray[]=$value;
}
}
$rs=$db->prepare('SELECT * FROM mytable'.(count($paramArray) ? ' WHERE '.join(' AND ',$sql) : ''));
$result=$rs->execute($paramArray);
if($result) {
return $rs;
}
return false;
}
?>
johniskew
22-Feb-2007 11:03
22-Feb-2007 11:03
If you need to create variable sql statements in a prepare statement...for example you may need to construct a sql query with zero, one, two, etc numbers of arguments...here is a way to do it without a lot of if/else statements needed to glue the sql together:
<?php
public function matchCriteria($field1=null,$field2=null,$field3=null) {
$db=DB::conn();
$sql=array();
$paramArray=array();
if(!empty($field1)) {
$sql[]='field1=?';
$paramArray[]=$field1;
}
if(!empty($field2)) {
$sql[]='field2=?';
$paramArray[]=$field2;
}
if(!empty($field3)) {
$sql[]='field3=?';
$paramArray[]=$field3;
}
$rs=$db->prepare('SELECT * FROM mytable'.(count($paramArray)>0 ? ' WHERE '.join(' AND ',$sql) : ''));
$result=$rs->execute($paramArray);
if($result) {
return $rs;
}
return false;
}
?>