PDO->prepare()

Prepares a statement for execution and returns a statement object ()

Example 1763. Prepare an SQL statement with named parameters

copy to clipboard

<?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 1764. Prepare an SQL statement with question mark parameters

copy to clipboard

<?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();
?>

Code Examples / Notes » pdo_prepare

www.onphp5.com

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

johniskew

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; } ?>

greg maclellan

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; } ?>

Change Language

PDO->beginTransaction()
PDO->commit()
PDO->__construct()
PDO->errorCode()
PDO->errorInfo()
PDO->exec()
PDO->getAttribute()
PDO->getAvailableDrivers()
PDO->lastInsertId()
PDO->prepare()
PDO->query()
PDO->quote()
PDO->rollBack()
PDO->setAttribute()
PDOStatement->bindColumn()
PDOStatement->bindParam()
PDOStatement->bindValue()
PDOStatement->closeCursor()
PDOStatement->columnCount()
PDOStatement->errorCode()
PDOStatement->errorInfo()
PDOStatement->execute()
PDOStatement->fetch()
PDOStatement->fetchAll()
PDOStatement->fetchColumn()
PDOStatement->fetchObject()
PDOStatement->getAttribute()
PDOStatement->getColumnMeta()
PDOStatement->nextRowset()
PDOStatement->rowCount()
PDOStatement->setAttribute()
PDOStatement->setFetchMode()