w3resource

PHP PDO

Introduction

Due to its simplicity and ease of use, PHP is a widely-used open source general-purpose scripting language. PHP is used for creating interactive and dynamic web pages quickly and can access a wide range of relational database management systems such as MySQL, PostgreSQL, and SQLite. Many of us already access MySQL databases by using either the MySQL or MySQLi extensions. As of version 5.1 PHP provides new database connection abstraction library, PHP Data Objects (PDO).

Contents:

What is PDO?

  • PDO - PHP Data Object.
  • A set of PHP extensions that provide a core PDO class and database specific drivers.
  • Provides a vendor-neutral lightweight data-access abstraction layer.
  • Focus on data access abstraction rather than database abstraction.
  • PDO requires the new object oriented features in the core of PHP 5, therefore it will not run with earlier versions of PHP.

Installing PDO

PDO is dividing into two components:
- Core which provides the interface.
- Drivers to access particular driver.

Installing PDO on Unix systems:
-- PDO (Core) and the PDO_SQLITE driver (SQLITE driver) is enabled by default as of PHP 5.1.0. To access other databases you must enable the PDO driver.
-- To install PDO as a shared module the php.ini needs to be updated so that the PDO extension will be loaded automatically when PHP runs. You also need to enable other database specific drivers and they must be listed after the pdo.so line, as PDO must be initialized before the database-specific extensions can be loaded. If you built PDO and the database-specific extensions statically, you can skip this step:
extension=pdo.so

Installing PDO on Windows systems:
-- PDO and all the major drivers ship with PHP as shared extensions, and simply need to be activated by editing the php.ini file :
extension=php_pdo.dll.
This step is not necessary for PHP 5.3 and above, as a DLL is no longer required for PDO.
-- Next, choose the other database-specific DLL files and either use dl() to load them at runtime, or enable them in php.ini below php_pdo.dll.

To get the effect of a new configuration in php.ini file you will need to restart PHP.

Predefined Constants

Supported Database

PDO interface is available in the following drivers:

Database name Driver name
Cubrid PDO_CUBRID
FreeTDS / Microsoft SQL Server / Sybase PDO_DBLIB
Firebird/Interbase 6 PDO_FIREBIRD
IBM DB2 PDO_IBM
IBM Informix Dynamic Server PDO_INFORMIX
MySQL 3.x/4.x/5.x PDO_MYSQL
Oracle Call Interface PDO_OCI
ODBC v3 (IBM DB2, unixODBC and win32 ODBC) PDO_ODBC
PostgreSQL PDO_PGSQL
SQLite 3 and SQLite 2 PDO_SQLITE
Microsoft SQL Server / SQL Azure PDO_SQLSRV
4D PDO_4D

Sample database, table, table structure, table records for various examples

MySQL:

Database Name: hr
Host Name: localhost
Database user: root
Password: ' '

Structure of the table: user_details

use details structure

 

Records of the table: user_details

records user details

 

PostgreSQL:

Date base Name: postgres
Host Name: localhost
Database user: postgres
Password: abc123

Structure of the table: user_details

postgre table structure

 

Records of the table: user_details

postgresql table records

 

The PDO class

The class represents a connection between PHP and a database server.

Syntax:

PDO {
      __construct ( string $dsn [, string $username [, string $password [, array $driver_options ]]] ) 
	bool beginTransaction ( void ) 
	bool commit ( void ) 
	mixed errorCode ( void ) 
	array errorInfo ( void ) 
	int exec ( string $statement ) 
	mixed getAttribute ( int $attribute ) 
	static array getAvailableDrivers ( void ) 
	bool inTransaction ( void ) 
	string lastInsertId ([ string $name = NULL ] ) 
	PDOStatement prepare ( string $statement [, array $driver_options = array() ] ) 
	PDOStatement query ( string $statement ) 
	string quote ( string $string [, int $parameter_type = PDO::PARAM_STR ] ) 
	bool rollBack ( void ) 
	bool setAttribute ( int $attribute , mixed $value ) }mp($var_name);
} 

Details of the PDO class methods :

PDO::__construct

Creates a PDO instance representing a connection to a database.

Syntax:

PDO::__construct() ( string $dsn [, string $username [, string $password [, array $driver_options ]]] )

Parameters :

dsn - The Data Source Name, or DSN, contains the information required to connect to the database. The string contains the prefix name (e.g. pgsql for PostgreSQL database), a colon, and the server keyword.
username - A string that contains the user's name. This parameter is optional for some PDO drivers.
password - A string that contains the user's password. This parameter is optional for some PDO drivers.
driver_options - Optional. A key=>value array of driver-specific connection options.

Return Value:
Returns a PDO object on success. If failure, returns a PDOException object.

Database Connections

Connections are established by creating instances of the PDO base class. It doesn't matter which driver you want to use; you always use the PDO class name. The constructor accepts parameters for specifying the database source (known as the DSN) and optionally for the username and password (if any).

MySQL connection

<?php
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
?>        

PostgreSQL connection

<?php
$dbuser = 'postgres';
$dbpass = 'abc123';
$host = 'localhost';
$dbname='postgres';
$dbh = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);
?>

Handling connection errors

If there are any connection errors, a PDOException object will be thrown. You may catch the exception if you want to handle the error condition, or you can leave it to global exception handler which can be set up via set_exception_handler().

MySQL:

Here the user id is wrong.

<?php
try {
$dbhost = 'localhost';
$dbuser = 'roott';
$dbpass = '';
$dbh = new PDO('mysql:host=$dbhost;dbname=hr', $dbuser, $dbpass);
}catch (PDOException $e){
 echo "Error!: " . $e->getMessage() . "<br/>";
 die();
 }
?>

Output:

Error : SQLSTATE[28000] [1045] Access denied for user 'roott'@'localhost' (using password: NO)

PostgreSQL:

Here the database name is wrong.

<?php
$dbuser = 'postgress';
$dbpass = 'abc123';
$host = 'localhost';
$dbname='postgres';
$dbh = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e){
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
?>

Output:

Error: SQLSTATE[08006] [7] could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? FATAL: password authentication failed for user "postgress"

Closing a connection

<?php>
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// use the connection here
// Following command close the connection. 
$dbh = null;
?>

Persistent connections

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

MySQL:

<?php
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass, array(
PDO::ATTR_PERSISTENT => true));	 
?>

PostgreSQL:

<?php
$dbuser = 'postgres';
$dbpass = 'abc123';
$host = 'localhost';
$dbname='postgres';
$dbh = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass, array(
PDO::ATTR_PERSISTENT => true));	
?>

PDO::beginTransaction

Turns off auto-commit mode and begins a transaction. The transaction begins with PDO::beginTransaction and will end when PDO::commit or PDO::rollback is called.

Syntax:

bool PDO::beginTransaction ( void )

Return Value:
Returns TRUE on success or FALSE on failure.

Example:
The following example a MySQL database called hr and table called user_details have used. It starts a transaction and then executes a command to add one row into the table user_details. The command is sent to the database and the transaction is explicitly ended with PDO::commit.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}	
$connec->beginTransaction();
$result = $connec->exec("INSERT INTO user_details (userid, password, fname, lname, gender, dtob, country, user_rating, emailid) VALUES
('abcd123', '123@John', 'John', 'ray', 'M', '1992-06-11', 'USA', '130', '[email protected]')");
$connec->commit();
echo $result;	
?> 

PDO::commit

Commits a transaction, returning the database connection to auto-commit mode until the next call to PDO::beginTransaction() starts a new transaction.

Syntax:

bool PDO::commit ( void )

Return Value:
Returns TRUE on success or FALSE on failure.

Example:
See previous (PDO::beginTransaction) example.

PDO::errorCode

PDO::errorCode retrieves the SQLSTATE (a two characters class value followed by a three characters subclass value) associated with the last operation on the database handle.

Syntax:

mixed PDO::errorCode();

Return Value:
Returns a five-char SQLSTATE as a string, or NULL if there was no operation on the statement handle.

Example:

In this example, the name of the column is misspelled (genderr instead of gender), causing an error. errorCode() displays the error.

<?php
try{
$dbuser = 'postgres'; $dbpass = 'abc123'; $host = 'localhost'; $dbname='postgres'; $connec = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass); } catch (PDOException $e) { echo "Error : " . $e->getMessage() . "<br/>"; die(); } $query = "SELECT * FROM user_details where genderr='M'"; $connec->query($query); echo $connec->errorCode(); ?>

Output:

42S22

PDO::errorInfo

Retrieves extended error information associated with the last operation on the database handle.

Syntax:

array PDO::errorInfo();

Return Value:
An array of error information about the last operation performed by this database handle. The array consists of the following fields:

0 : The SQLSTATE error code.
1 : The driver-specific error code.
2 : The driver-specific error message.

Example:
In the following example (PostgreSQL database is used), the name of the column is misspelled (genderr instead of gender), causing an error, which is then reported.

<?php
try{
$dbuser = 'postgres';
$dbpass = 'abc123';
$host = 'localhost';
$dbname='postgres';
$connec = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);
}
catch (PDOException $e)
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
} 
$query = "SELECT * FROM user_details where genderr='M'";
$connec->query($query);
echo $connec->errorCode();
print_r ($connec->errorInfo());	
?>

Output:

42703Array ( [0] => 42703 [1] => 7 [2] => ERROR: column "genderr" does not exist LINE 1: SELECT * FROM user_details where genderr='M' ^ )

PDO::exec

Execute an SQL statement and return the number of rows affected by the statement.

Syntax:

int PDO::exec ($statement)

Parameters :
statement - An SQL statement to prepare and execute.

Return Value:
An integer reporting the number of rows affected. If no rows were affected, PDO::exec() returns 0.

Example:

In the following example (PostgreSQL database is used), the name of the column is misspelled (genderr instead of gender), causing an error, which is then reported.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
/*  Delete some rows from the allcountry table  */
$count = $connec->exec("DELETE FROM allcountry WHERE country_id = 'AR'");
/* Return number of rows */
echo("Number of deleted rows in allcountry table : ". $count);
?>

Output:

Number of deleted rows in allcountry table : 1

PDO::getAttribute

Retrieves the value of a predefined PDO or driver attribute.

Syntax:

mixed PDO::getAttribute ( $attribute )

Parameters:
One of the PDO::ATTR_* constants. The constants that apply to database connections are as follows:
PDO::ATTR_AUTOCOMMIT
PDO::ATTR_CASE
PDO::ATTR_CLIENT_VERSION
PDO::ATTR_CONNECTION_STATUS
PDO::ATTR_DRIVER_NAME
PDO::ATTR_ERRMODE
PDO::ATTR_ORACLE_NULLS
PDO::ATTR_PERSISTENT
PDO::ATTR_PREFETCH
PDO::ATTR_SERVER_INFO
PDO::ATTR_SERVER_VERSION
PDO::ATTR_TIMEOUT

Return Value:
A successful call returns the value of the requested PDO attribute. On failure, returns null.

Example:
The following example (PostgreSQL database is used) retrieving database connection attributes.

<?php
try{
$dbuser = 'postgres';
$dbpass = 'abc123';
$host = 'localhost';
$dbname='postgres';
$conn = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);
}
catch (PDOException $e)
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
attributes = array(
"AUTOCOMMIT", "ERRMODE", "CASE", "CLIENT_VERSION", "CONNECTION_STATUS",
"ORACLE_NULLS", "PERSISTENT", "PREFETCH", "SERVER_INFO", "SERVER_VERSION",
"TIMEOUT"
);
foreach ($attributes as $val) 
{
echo "PDO::ATTR_$val: ";
echo $conn->getAttribute(constant("PDO::ATTR_$val")) . "<br>";
}
?>

Output:

PDO::ATTR_AUTOCOMMIT:
 PDO::ATTR_ERRMODE: 0
 PDO::ATTR_CASE: 0
 PDO::ATTR_CLIENT_VERSION: 8.3.6
 PDO::ATTR_CONNECTION_STATUS: Connection OK; waiting to send.
 PDO::ATTR_ORACLE_NULLS: 0
 PDO::ATTR_PERSISTENT:
 PDO::ATTR_PREFETCH:
 PDO::ATTR_SERVER_INFO: PID: 5940; Client Encoding: UTF8; Is Superuser: on; Session Authorization: postgres; Date Style: ISO, MDY
 PDO::ATTR_SERVER_VERSION: 9.1.3
 PDO::ATTR_TIMEOUT:

PDO::getAvailableDrivers

Return an array of available PDO drivers in your PHP installation.

Syntax:

array PDO::getAvailableDrivers ();

Return Value:
An array with the list of PDO drivers.

Example:

The following example returns an array of available PDO driver names.

<?php
print_r(PDO::getAvailableDrivers());
?>

Output:

Array ( [0] => mysql [1] => sqlite )

PDO::inTransaction

Checks if a transaction is currently active within the driver. This method only works for database drivers that support transactions.

Syntax:

bool PDO::inTransaction ( void )

Return Value:
Returns TRUE if a transaction is currently active, and FALSE if not.

PDO::lastInsertId

Returns the identifier of the last inserted row or sequence value into a table in the database.

Syntax:

string PDO::lastInsertId ([ string $name = NULL ] )

Return Value:
If a sequence name was not specified for the name parameter, PDO::lastInsertId() returns a string representing the row ID of the last row that was inserted into the database.
If a sequence name was specified for the name parameter, PDO::lastInsertId() returns a string representing the last value retrieved from the specified sequence object.
If the PDO driver does not support this capability, PDO::lastInsertId() triggers an IM001 SQLSTATE.

Example:

The following example (PostgreSQL database is used) returns the ID of the last inserted row or sequence value.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$result = $connec->exec("INSERT INTO user_details (userid, password, fname, lname, gender, dtob, country, user_rating, emailid) VALUES
('abcd123', '123@John', 'John', 'ray', 'M', '1992-06-11', 'USA', '130', '[email protected]')");
$lastRow = $connec->lastInsertId('user_details');
echo $lastRow ;
?>

PDO::prepare

Prepares a statement for execution.

Syntax:

PDO::prepare ( string $statement [, array $driver_options = array() ] )

Parameters:
statement: A string contains a valid SQL statement.
driver_options: An array containing an attribute name and value (key=>value pairs ).

Return Value:
Returns a PDOStatement object on success. On failure, returns a PDOException object, or false depending on the value of PDO::ATTR_ERRMODE.

Example - 1:

The following example prepares an SQL statement with named parameters.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT fname, lname, country FROM user_details
        WHERE country = :country';
$sth = $connec->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':country' => 'Germany'));
$c = $sth->fetchAll();
print_r($c);
?>

Output:

Array ( [0] => Array ( [fname] => Diana [0] => Diana [lname] => Lorentz [1] => Lorentz [country] => Germany [2] => Germany ) )

Example - 2:

The following example prepares an SQL statement with question mark parameters.

<?php
try 
{
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$sql = $connec->prepare('SELECT fname, lname, country FROM user_details
       WHERE country = ?');
$sql->execute(array('Germany'));
$c = $sql->fetchAll();
print_r($c);
?>

Output:

Array ( [0] => Array ( [fname] => Diana [0] => Diana [lname] => Lorentz [1] => Lorentz [country] => Germany [2] => Germany ) )

PDO::query

Executes an SQL query and returns a result set as a PDOStatement object.

Syntax:

PDOStatement PDO::query ( string $statement )
PDOStatement PDO::query ( string $statement , int $PDO::FETCH_COLUMN , int $colno )
PDOStatement PDO::query ( string $statement , int $PDO::FETCH_CLASS , string $classname , array $ctorargs )
PDOStatement PDO::query ( string $statement , int $PDO::FETCH_INTO , object $object )

Parameters:
statement: The SQL statement to execute.

Return Value:
PDO::query() returns a PDOStatement object, or FALSE on failure.

Example:

In the following example PDO::query() iterate over the rowset returned by a SELECT statement.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$sql = 'SELECT fname, lname, country FROM user_details ORDER BY country';
foreach ($connec->query($sql) as $row) 
{
print $row['fname'] . " ";
print $row['lname'] . "-->";
print $row['country'] . "<br>";
}
?>

Output:

Diana Lorentz-->Germany
          Palash Ghosh-->INDIA
    Scott Rayy-->USA

PDO::quote

Place quotes around the input string for use in a query.

Syntax:

string PDO::quote ( string $string [, int $parameter_type = PDO::PARAM_STR ] )

Parameters:
string - The string to be quoted.
parameter_type - Provides a data type hint for drivers that have alternate quoting styles.

Return Value:
A quoted string that can be passed to an SQL statement, or false if failure.

Example:
Following example shows how to quote a normal string.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$string = 'w3resource'; 
echo "Unquoted string : ". $string . "<br />";
echo "Quoted string : ". $connec->quote($string) . "<br />";
?>

Output:

 Unquoted string: w3resource
  Quoted string : 'w3resource'

PDO::rollBack

Rolls back the current transaction, as initiated by PDO::beginTransaction(). A PDOException will be thrown if no transaction is active.

Syntax:

bool PDO::rollBack ( void )

Return Value:
TRUE if the method call succeeded, FALSE otherwise.

Example:

Following example begins a transaction and issues a DROP statement before rolling back the changes. In MySQL the DROP TABLE statement automatically commits the transaction, therefore nothing will roll back.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}	
$connec->beginTransaction();
$sth = $connec->exec("DROP TABLE user_detail");
$connec->rollback();
?>

PDO::setAttribute

Set an attribute.

Syntax:

bool PDO::setAttribute ( int $attribute , mixed $value )

Here is a list of some of the available generic attributes:

  • PDO::ATTR_CASE: Force column names to a specific case.
  • PDO::CASE_LOWER: Force column names to lower case.
  • PDO::CASE_NATURAL: Leave column names as returned by the database driver.
  • PDO::CASE_UPPER: Force column names to upper case.
  • PDO::ATTR_ERRMODE: Error reporting.
  • PDO::ERRMODE_SILENT: Just set error codes.
  • PDO::ERRMODE_WARNING: Raise E_WARNING.
  • PDO::ERRMODE_EXCEPTION: Throw exceptions.
  • PDO::ATTR_ORACLE_NULLS (available with all drivers, not just Oracle): Conversion of NULL and empty strings.
  • PDO::NULL_NATURAL: No conversion.
  • PDO::NULL_EMPTY_STRING: Empty string is converted to NULL.
  • PDO::NULL_TO_STRING: NULL is converted to an empty string.
  • PDO::ATTR_STRINGIFY_FETCHES: Convert numeric values to strings when fetching. Requires bool.
  • PDO::ATTR_STATEMENT_CLASS: Set user-supplied statement class derived from PDOStatement. Cannot be used with persistent PDO instances. Requires array(string classname, array(mixed constructor_args)).
  • PDO::ATTR_TIMEOUT: Specifies the timeout duration in seconds. Not all drivers support this option, and it's meaning may differ from driver to driver. For example, sqlite will wait for up to this time value before giving up on obtaining an writable lock, but other drivers may interpret this as a connect or a read timeout interval.
  • PDO::ATTR_AUTOCOMMIT (available in OCI, Firebird and MySQL): Whether to autocommit every single statement.
  • PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE), or to try to use native prepared statements (if FALSE).
  • PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (available in MySQL): Use buffered queries.
  • PDO::ATTR_DEFAULT_FETCH_MODE: Set default fetch mode.

Return Value:
Returns TRUE on success or FALSE on failure.

Example:

Following example shows how to set the PDO::ATTR_ERRMODE attribute.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$attributes1 = array( "ERRMODE" );
foreach ( $attributes1 as $val ) {
echo "PDO::ATTR_$val: ";
var_dump ($conn->getAttribute( constant( "PDO::ATTR_$val" ) ));
}
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$attributes1 = array( "ERRMODE" );
foreach ( $attributes1 as $val ) 
{
  echo "PDO::ATTR_$val: ";
  var_dump ($conn->getAttribute( constant( "PDO::ATTR_$val" ) ));
}
?>

Output:

PDO::ATTR_ERRMODE:

PDOStatement class

Represents a prepared statement and, after the statement is executed, an associated result set.

Details of the Class :


PDOStatement implements Traversable 
{ /* Properties */
 readonly string $queryString; 
 /* Methods */ 
 public bool bindColumn ( mixed $column , mixed &$param [, int $type [, int $maxlen [, mixed $driverdata ]]] ) 
 public bool bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] ) 
 public bool bindValue ( mixed $parameter , mixed $value [, int $data_type = PDO::PARAM_STR ] ) 
 public bool closeCursor ( void ) 
 public int columnCount ( void ) 
 public void debugDumpParams ( void ) 
 public string errorCode ( void ) 
 public array errorInfo ( void ) 
 public bool execute ([ array $input_parameters ] )
 public mixed fetch ([ int $fetch_style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]] ) 
 public array fetchAll ([ int $fetch_style [, mixed $fetch_argument [, array $ctor_args = array() ]]] ) 
 public string fetchColumn ([ int $column_number = 0 ] ) 
 public mixed fetchObject ([ string $class_name = "stdClass" [, array $ctor_args ]] ) 
 public mixed getAttribute ( int $attribute ) 
 public array getColumnMeta ( int $column ) 
 public bool nextRowset ( void ) 
 public int rowCount ( void ) 
 public bool setAttribute ( int $attribute , mixed $value ) 
 public bool setFetchMode ( int $mode ) 
}

PDOStatement::bindColumn

Binds a PHP variable to a column in a result set.

Syntax:

bool PDOStatement::bindColumn ( mixed $column , mixed &$param [, int $type [, int $maxlen [, mixed $driverdata ]]] )

Parameters:

Name Description Type
column The number of the column (1- indexed) or the name of the column in the result set. mixed
param The name of the PHP variable to which the column will be bound. mixed
type Data type of the parameter, specified by the PDO::PARAM_* constants. int
maxLen A hint for pre-allocation (optional). int
driverdata Optional parameter(s) for the driver. mixed

Return Value:
Returns TRUE on success or FALSE on failure.

Example:
The following example shows how a variable can be bound to a column in a result set.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$query = "SELECT fname, lname, dtob, country, emailid FROM user_details where gender = 'M'";
$stmt = $conn->prepare($query);
$stmt->execute();
$stmt->bindColumn('emailid', $email);
while ( $row = $stmt->fetch( PDO::FETCH_BOUND ) )
{
echo "$email"."<br>";
}
?>

Output:

[email protected]
  [email protected]
  [email protected]

PDOStatement::bindParam

Binds a parameter to the specified variable name.

Syntax:

 bool PDOStatement::bindParam ( mixed $parameter, mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )

Parameters:

Name Description Type
parameter Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. mixed
variable The name of the PHP variable to bind to the SQL statement parameter. mixed
data_type Optional, PDO::PARAM_* constant. int
length Length of the data type. int
driver_options The optional (mixed) driver-specific options. mixed

Return Value:
Returns TRUE on success or FALSE on failure.

Example - 1:
The following example shows how to execute a prepared statement with named placeholders.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e){
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$user_rating = 100;
$gender = 'M';
$sth = $conn->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details
WHERE user_rating > :user_rating AND gender = :gender');
$sth->bindParam(':user_rating', $user_rating, PDO::PARAM_INT);
$sth->bindParam(':gender', $gender, PDO::PARAM_STR, 1);
$sth->execute(); 
?>

Example - 2 :
The following example shows how to execute a prepared statement with question mark placeholders.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e){
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$user_rating = 100;
$gender = 'M';
$sth = $conn->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details
WHERE user_rating > ? AND gender = ?');
$sth->bindParam(1, $user_rating, PDO::PARAM_INT);
$sth->bindParam(2, $gender, PDO::PARAM_STR, 1);$sth->execute(); 
?>

PDOStatement::bindValue

Binds a value to a named or question mark placeholder in the SQL statement.

Syntax:

bool PDOStatement::bindValue ( mixed $parameter , mixed $value [, int $data_type = PDO::PARAM_STR ] )

Parameters:

Name Description Type
parameter Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. mixed
value The value to bind to the parameter. mixed
data_type Optional, PDO::PARAM_* constant. int

Return Value:
Returns TRUE on success or FALSE on failure.

Example - 1:
The following example shows how to execute a prepared statement with named placeholders.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e){
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$user_rating = 100;
$gender = 'M';
$sth = $conn->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details
WHERE user_rating > :user_rating AND gender = :gender');
$sth->bindValue(':user_rating', $user_rating, PDO::PARAM_INT);
$sth->bindValue('gender', $gender, PDO::PARAM_STR, 1);
print_r($sth->execute()); 
?>

Output:

Array ( [0] => Array ( [fname] => Diana [0] => Diana [lname] => Lorentz [1] => Lorentz [country] => Germany [2] => Germany ) ) 

Example - 2:
The following example shows how to execute a prepared statement with question mark placeholders.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e){
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$user_rating = 100;
$gender = 'M';
$sth = $conn->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details
WHERE user_rating > ? AND gender = ?');
$sth->bindValue(':user_rating', $user_rating, PDO::PARAM_INT);
$sth->bindValue('gender', $gender, PDO::PARAM_STR, 1);
print_r($sth->execute());
?>

Output:

Array ( [0] => Array ( [fname] => Palash [0] => Palash [lname] => Ghosh [1] => Ghosh [country] => INDIA [2] => INDIA ) )

PDOStatement::closeCursor

Closes the cursor, enabling the statement to be executed again.

Syntax:

bool PDOStatement::closeCursor ( void )

Return Value:
Returns TRUE on success or FALSE on failure.

Example - 1:

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
/* Create a PDOStatement object */
$stmt = $connec->prepare('SELECT * from user_details');
/* Create a second PDOStatement object */
$otherStmt = $connec->prepare("SELECT * from usser_details where gender ='M'");
/* Execute the first statement */
$stmt->execute();
/* Fetch only the first row from the results */
$stmt->fetch();
/* The following call to closeCursor() may be required by some drivers */
$stmt->closeCursor();
/* Now we can execute the second statement */
$otherStmt->execute();
?>

PDOStatement::columnCount

Returns the number of columns in a result set.

Syntax:

int PDOStatement::columnCount ( void )

Return Value:
Returns TRUE on success or FALSE on failure.

Example - 1:
The following example displays the number of columns of a particular table.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) 
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$query = "select * from user_details";
$stmt = $connec->prepare( $query );
$stmt->execute();
echo "No. of columns: ".$stmt->columnCount();
?>

Output:

No. of columns: 9

PDOStatement::debugDumpParams

Dump an SQL prepared command.

p>Syntax:

void PDOStatement::debugDumpParams ( void )

Return Value:
No value is returned.

Example - 1:
Here is an example of PDOStatement::debugDumpParams().

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) 
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$user_rating = 100;
$gender = 'M';
$sth = $connec->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details
WHERE user_rating > :user_rating AND gender = :gender');
$sth->bindParam(':user_rating', $user_rating, PDO::PARAM_INT);
$sth->bindParam(':gender', $gender, PDO::PARAM_STR, 12);
$sth->execute();
$sth->debugDumpParams(); 
?>

Output:

SQL: [116] SELECT fname, lname, dtob, country, emailid FROM user_details WHERE user_rating > :user_rating AND gender = :gender Params: 2 Key: Name: [12] :user_rating paramno=-1 name=[12] ":user_rating" is_param=1 param_type=1 Key: Name: [7] :gender paramno=-1 name=[7] ":gender" is_param=1 param_type=2

PDOStatement::errorCode

Fetch the SQLSTATE of the most recent operation on the database statement object.

Syntax:

 public string PDOStatement::errorCode ( void )

Return Value:
Same to PDO::errorCode(), except that PDOStatement::errorCode() only retrieves error codes for operations performed with PDOStatement objects.

Example - 1:
In the following example, SQL query has an error (wrong column name). errorCode() displays the error.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) 
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$stmt = $conn->prepare('SELECT fname, tname FROM user_details');
$stmt->execute();
echo "PDOStatement::errorCode(): "."<br>";
print_r ($stmt->errorcode());
?>

Output:

PDOStatement::errorCode():
    42S22

PDOStatement::errorInfo

Retrieves error information associated with the last operation on the statement handle.

Syntax:

array PDOStatement::errorInfo ( void )

Parameters:

Name Description Type
parameter
Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. mixed
value The value to bind to the parameter. mixed
data_type Optional, PDO::PARAM_* constant. int

Return Value:
PDOStatement::errorInfo() returns an array of error information about the last operation performed by this statement handle. The array consists of the following fields:

0 - The SQLSTATE error code.
1 - The driver-specific error code.
2 - The driver-specific error message.

Example - 1 :
In the following example, SQL statement has an error, which is displayed by errorinfo().

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) 
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$stmt = $conn->prepare('SELECT fname, tname FROM user_details');
$stmt->execute();
print_r ($stmt->errorInfo());
?>

Output:

Array ( [0] => 42S22 [1] => 1054 [2] => Unknown column 'tname' in 'field list' )

PDOStatement::execute

Executes a prepared statement.

Syntax:

bool PDOStatement::execute ([ array $input_parameters ] )

Parameters:

Name Description Type
input_parameters An array containing the values for parameter markers. array

Return Value:
Returns TRUE on success or FALSE on failure.

Example - 1:
The following example executes a prepared with bound variables.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e){
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$user_rating = 100;
$gender = 'M';
$sth = $connec->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details
WHERE user_rating > :user_rating AND gender = :gender');
$sth->bindParam(':user_rating', $user_rating, PDO::PARAM_INT);
$sth->bindParam(':gender', $gender, PDO::PARAM_STR, 12);
$sth->execute();
while ( $row = $sth->fetch( PDO::FETCH_ASSOC ) )
{
echo "$row[fname]"." "."$row[lname]";
} 
?>

Output:

John ray

PDOStatement::fetch

Fetches the next row from a result set.

Syntax :

public mixed PDOStatement::fetch ([ int $fetch_style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]] )

Parameters:

Name Description Type
parameter Controls how the next row will be returned to the caller. This value must be one of the PDO::FETCH_* constants (see the constants details), defaulting to the value of PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to PDO::FETCH_BOTH). int
cursor_orientation This value must be one of the PDO::FETCH_ORI_* constants, defaulting to PDO::FETCH_ORI_NEXT. int
offset For a PDOStatement object representing a scrollable cursor for which the cursor_orientation parameter is set to PDO::FETCH_ORI_ABS, this value specifies the absolute number of the row in the result set that shall be fetched. int

Return Value:
The return value of this function on success depends on the fetch type. In all cases, FALSE is returned on failure.

Example - 1:
The following example shows how to fetch rows using different fetch styles.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$sth = $conn->prepare("SELECT fname, lname FROM user_details");
$sth->execute();
/* Exercise PDOStatement::fetch styles */
echo "PDO::FETCH_ASSOC: "."<br>";
echo "Return next row as an array indexed by column name"."<br>";
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
echo("<br>");
echo "PDO::FETCH_BOTH: "."<br>";
echo "Return next row as an array indexed by both column name and number"."<br>";
$result = $sth->fetch(PDO::FETCH_BOTH);
print_r($result);
echo("<br>");
echo "PDO::FETCH_LAZY: "."<br>";
echo "Return next row as an anonymous object with column names as properties"."<br>";
$result = $sth->fetch(PDO::FETCH_LAZY);
print_r($result);
echo("<br>");
echo "PDO::FETCH_OBJ: "."<br>";
echo "Return next row as an anonymous object with column names as properties"."<br>";
$result = $sth->fetch(PDO::FETCH_OBJ);
echo $result->fname;
?>

Output:

 PDO::FETCH_ASSOC:
      Return next row as an array indexed by column name
      Array ( [fname] => Scott [lname] => Rayy )
      PDO::FETCH_BOTH:
      Return next row as an array indexed by both column name and number
      Array ( [fname] => Palash [0] => Palash [lname] => Ghosh [1] => Ghosh ) 
      PDO::FETCH_LAZY: 
      Return next row as an anonymous object with column names as properties
      PDORow Object ( [queryString] => SELECT fname, lname FROM user_details [fname] => Diana [lname] => Lorentz ) 
      PDO::FETCH_OBJ: 
      Return next row as an anonymous object with column names as properties
    John

PDOStatement::fetchAll

Returns an array containing the rows in a result set.

Syntax:

array PDOStatement::fetchAll ([ int $fetch_style [, mixed $fetch_argument [, array $ctor_args = array() ]]] )

Parameters:

Name Description Type
fetch_style Specifying the format of the row data. mixed
fetch_argument This argument have a different meaning depending on the value of the fetch_style parameter
PDO::FETCH_COLUMN: Returns the indicated 0-indexed column.
PDO::FETCH_CLASS: Returns instances of the specified class, mapping the columns of each row to named properties in the class.
PDO::FETCH_FUNC: Returns the results of calling the specified function, using each row's columns as parameters in the call.
mixed
ctor_args Arguments of custom class constructor when the fetch_style parameter is PDO::FETCH_CLASS. int

Return Value:
An array of the remaining rows in the result set, or false if the method call fails.

Example - 1:
The following example fetches all remaining rows in a result set

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) 
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$sth = $connec->prepare("SELECT fname, lname FROM user_details");
$sth->execute();
/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result); 
?>

Output:

Fetch all of the remaining rows in the result set: Array ( [0] => Array ( [fname] => Scott [0] => Scott [lname] => Rayy [1] => Rayy ) [1] => Array ( [fname] => Palash [0] => Palash [lname] => Ghosh [1] => Ghosh ) [2] => Array ( [fname] => Diana [0] => Diana [lname] => Lorentz [1] => Lorentz ) [3] => Array ( [fname] => John [0] => John [lname] => ray [1] => ray ) )

 

PDOStatement::fetchColumn

Returns one column in a row from the next row of a result set.

Syntax:

string PDOStatement::fetchColumn ([ int $column_number = 0 ] )

Parameters:

Name Description Type
column_number An optional integer indicating the zero-based column number. The default is 0 (the first column in the row). mixed

Return Value:
Returns TRUE on success or FALSE on failure.

Example - 1:
The following example returns one column in a row.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) 
{echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$stmt = $connec->query("select * from user_details where gender='M';");
while ( $result = $stmt->fetchColumn(1)) 
{
echo $result . "<br>"; 
}
?>

Output:

123@sco
      dloeiu@&3
    123@John

 

PDOStatement::fetchObject

Retrieves the next row as an object.

Syntax:

public mixed PDOStatement::fetchObject ([ string $class_name = "stdClass" [, array $ctor_args ]] )

Parameters:

Name Description Type
class_name The name of the class to create. string
ctor_args An array with arguments to a custom class constructor. array

Return Value:
On success, returns an object with an instance of the class. Returns false on failure.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) 
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$stmt = $connec->query( "select * from user_details where gender='M';" );
$result = $stmt->fetchObject();
echo $result->fname;
?>

Output:

Scott

PDOStatement::getAttribute

Retrieve a statement attribute.

Syntax:

 mixed PDOStatement::getAttribute ( int $attribute )

Gets an attribute of the statement. Currently, no generic attributes exist but the only driver specific:

Return Value:
Returns the attribute value.

PDOStatement::getColumnMeta

Returns metadata for a column in a result set.

Syntax:

public array PDOStatement::getColumnMeta ( int $column )

Parameters:

Name Description Type
column The 0-indexed column in the result set. int

Return Value:
Returns an associative array.

Note:This function is EXPERIMENTAL.

Example - 1:
The following example shows the results of retrieving the metadata.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$select = $connec->query("select fname, lname from user_details where gender='M';");
$meta = $select->getColumnMeta(0);
var_dump($meta) 
?>

Output:

array(7) { ["native_type"]=> string(10) "VAR_STRING" ["pdo_type"]=> int(2) ["flags"]=> array(1) { [0]=> string(8) "not_null" } ["table"]=> string(12) "user_details" ["name"]=> string(5) "fname" ["len"]=> int(100) ["precision"]=> int(0) }

PDOStatement::nextRowset

Moves the cursor to the next rowset in a multi-rowset statement handle.

Syntax:

bool PDOStatement::nextRowset ( void )

Return Value:
Returns TRUE on success or FALSE on failure.

Example - 1:
The following example shows how the cursor moves to the next rowset in a multi-rowset statement.

<?php
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) 
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$query1 = "select region_name from regions where region_id>2;";
$query2 = "select fname from user_details where gender='M';";
$stmt = $connec->query( $query1 . $query2 );
$rowset1 = $stmt->fetchAll();
$stmt->nextRowset();
$rowset2 = $stmt->fetchAll();
var_dump($rowset1 );
echo "<br>";
var_dump($rowset2 );?>

Output:

array(2) { [0]=> array(2) { ["region_name"]=> string(5) "Asia " [0]=> string(5) "Asia " } [1]=> array(2) { ["region_name"]=> string(23) "Middle East and Africa " [0]=> string(23) "Middle East and Africa " } }
 array(3) { [0]=> array(2) { ["fname"]=> string(5) "Scott" [0]=> string(5) "Scott" } [1]=> array(2) { ["fname"]=> string(6) "Palash" [0]=> string(6) "Palash" } [2]=> array(2) { ["fname"]=> string(4) "John" [0]=> string(4) "John" }

PDOStatement::rowCount

Returns the number of rows affected (added, deleted, or changed) by the last statement.

Syntax:

public int PDOStatement::rowCount ( void )

Return Value:
Return the number of rows.

Example - 1:
The following example shows how to execute a prepared statement with named placeholders.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) 
{
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
/* Delete all rows from the alluser table *
$del = $connec->prepare('DELETE FROM alluser');
$del->execute();
/* Return number of rows that were deleted */
$count = $del->rowCount();
echo "Deleted ".$count." number of rows "; 
?>

Output:

Deleted 4 number of rows

PDOStatement::setAttribute

Set a statement attribute. Currently, no generic attributes are set but only driver specific:

Syntax:

bool PDOStatement::setAttribute ( int $attribute , mixed $value )

Return Value:

Returns TRUE on success or FALSE on failure.

PDOStatement::setFetchMode

Set the default fetch mode for this statement.

Syntax:

 public bool PDOStatement::setFetchMode ( int $mode )
 public bool PDOStatement::setFetchMode ( int $PDO::FETCH_COLUMN , int $colno )
 public bool PDOStatement::setFetchMode ( int $PDO::FETCH_CLASS , string $classname , array $ctorargs )
 public bool PDOStatement::setFetchMode ( int $PDO::FETCH_INTO , object $object )

Parameters:

Name Description Type
mode The fetch mode must be one of the PDO::FETCH_* constants. See the constants list. mixed
colno Column number. int
classname Class name. string
ctorargs Constructor arguments. array
object Object. object

Return Value:
Returns TRUE on success or FALSE on failure.

Example - 1:
The following example demonstrates the uses of PDO::FETCH_ASSOC, PDO::FETCH_NUM, PDO::FETCH_BOTH, PDO::FETCH_LAZY, PDO::FETCH_OBJ constants.

<?php
try {
$dbhost = 'localhost';
$dbname='hr';
$dbuser = 'root';
$dbpass = '';
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
$stmt1 = $conn->query( "select * from user_details where gender='M'" );
while ( $row = $stmt1->fetch())
{
echo($row['fname'] . "<br>");
}
echo "<br>"."PDO::FETCH_ASSOC -------------"."<br>";
$stmt = $conn->query( "select * from user_details where gender='M'" );
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$result = $stmt->fetch();
print_r( $result );
echo "<br>"."PDO::FETCH_NUM -------------"."<br>";
$stmt = $conn->query( "select * from user_details where gender='M'" );
$stmt->setFetchMode(PDO::FETCH_NUM);
$result = $stmt->fetch();
print_r( $result );
echo "<br>"."PDO::FETCH_BOTH -------------"."<br>";
$stmt = $conn->query( "select * from user_details where gender='M'" );
$stmt->setFetchMode(PDO::FETCH_BOTH);
$result = $stmt->fetch();
print_r( $result );
echo "<br>"."PDO::FETCH_LAZY -------------"."<br>";
$stmt = $conn->query( "select * from user_details where gender='M'" );
$stmt->setFetchMode(PDO::FETCH_LAZY);
$result = $stmt->fetch();
print_r( $result );
echo "<br>"."PDO::FETCH_OBJ -------------"."<br>";
$stmt = $conn->query( "select * from user_details where gender='M'" );
$stmt->setFetchMode(PDO::FETCH_OBJ);
$result = $stmt->fetch();
print_r( $result );
?>

Output:

Scott
Palash
John

PDO::FETCH_ASSOC -------------
Array ( [userid] => scott123 [password] => 123@sco [fname] => Scott [lname] => Rayy [gender] => M [dtob] => 1990-05-15 [country] => USA [user_rating] => 100 [emailid] => [email protected] )
PDO::FETCH_NUM -------------
Array ( [0] => scott123 [1] => 123@sco [2] => Scott [3] => Rayy [4] => M [5] => 1990-05-15 [6] => USA [7] => 100 [8] => [email protected] )
PDO::FETCH_BOTH -------------
Array ( [userid] => scott123 [0] => scott123 [password] => 123@sco [1] => 123@sco [fname] => Scott [2] => Scott [lname] => Rayy [3] => Rayy [gender] => M [4] => M [dtob] => 1990-05-15 [5] => 1990-05-15 [country] => USA [6] => USA [user_rating] => 100 [7] => 100 [emailid] => [email protected] [8] => [email protected] )
PDO::FETCH_LAZY -------------
PDORow Object ( [queryString] => select * from user_details where gender='M' [userid] => scott123 [password] => 123@sco [fname] => Scott [lname] => Rayy [gender] => M [dtob] => 1990-05-15 [country] => USA [user_rating] => 100 [emailid] => [email protected] )
PDO::FETCH_OBJ -------------
stdClass Object ( [userid] => scott123 [password] => 123@sco [fname] => Scott [lname] => Rayy [gender] => M [dtob] => 1990-05-15 [country] => USA [user_rating] => 100 [emailid] => [email protected] )

Previous: PHP Object Oriented Programming
Next: PHP Form handling



Follow us on Facebook and Twitter for latest update.