PHP PDO

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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?

  • Installing PDO

  • Predefined Constants

  • Supported Databases

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

  • The PDO class

  • Details of the PDO class methods

  • Details of PDOStatement class

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

.

Name Description Type
PDO::PARAM_BOOL
 
Represents a boolean data type. integer
PDO::PARAM_NULL Represents the SQL NULL data type. integer
PDO::PARAM_INT Represents the SQL INTEGER data type. integer
PDO::PARAM_STR Represents the SQL CHAR, VARCHAR, or other string data type. integer
PDO::PARAM_LOB  Represents the SQL large object data type. integer
PDO::PARAM_STMT Represents a recordset type. Not currently supported by any drivers. integer
PDO::PARAM_INPUT_OUTPUT Specifies that the parameter is an INOUT parameter for a stored procedure. You must bitwise-OR this value with an explicit PDO::PARAM_* data type. integer
PDO::FETCH_LAZY

Fetch method returns each row as an object with column names as properties returned in the result set. PDO::FETCH_LAZY creates the object variable names as they are accessed. Not valid inside PDOStatement::fetchAll().

integer
PDO::FETCH_ASSOC

Fetch method returns each row as an array indexed by column name in the result set. If there are multiple columns with same name in the result set PDO::FETCH_ASSOC returns only a single value per column name.

integer
PDO::FETCH_NAMED

Fetch method returns each row as an array indexed by both column name in the returned result set starting at column 0. If the result set contains multiple columns with the same name, PDO::FETCH_NAMED returns an array of values per column name.

integer
PDO::FETCH_NUM 

Fetch method returns each row as an array indexed by both column number in the returned result set starting at column 0.

integer
PDO::FETCH_BOTH

Fetch method returns each row as an array indexed by both column name and number in the returned result set starting at column 0.

integer
PDO::FETCH_OBJ

Fetch method returns each row as an object with property names that correspond to the column names returned in the result.

integer
PDO::FETCH_BOUND Specifies that the fetch method shall return TRUE and assign the values of the columns in the result set to the PHP variables to which they were bound with the PDOStatement::bindParam() or PDOStatement::bindColumn() methods. integer
PDO::FETCH_COLUMN  Specifies that the fetch method shall return only a single requested column from the next row in the result set. integer
PDO::FETCH_CLASS Specifies that the fetch method shall return a new instance of the requested class, mapping the columns to named properties in the class. integer
PDO::FETCH_INTO  Specifies that the fetch method shall update an existing instance of the requested class, mapping the columns to named properties in the class. integer
PDO::FETCH_FUNC Allows completely customize the way data is treated on the fly (only valid inside PDOStatement::fetchAll()). integer
PDO::FETCH_GROUP Group return by values. Usually combined with PDO::FETCH_COLUMN or PDO::FETCH_KEY_PAIR. integer
PDO::FETCH_UNIQUE Fetch only the unique values. integer
PDO::FETCH_KEY_PAIR Fetch a two-column result into an array where the first column is a key and the second column is the value. Available since PHP 5.2.3. integer
PDO::FETCH_CLASSTYPE Determine the class name from the value of first column. integer
PDO::FETCH_SERIALIZE As PDO::FETCH_INTO but object is provided as a serialized string. Available since PHP 5.1.0. Since PHP 5.3.0 the class constructor is never called if this flag is set. integer
PDO::FETCH_PROPS_LATE Call the constructor before setting properties. Available since PHP 5.2.0. integer
PDO::ATTR_AUTOCOMMIT If this value is FALSE, PDO attempts to disable autocommit so that the connection begins a transaction. integer
PDO::ATTR_PREFETCH Setting the prefetch size allows you to balance speed against memory usage for your application. Not all database/driver combinations support setting of the prefetch size. A larger prefetch size results in increased performance at the cost of higher memory usage. integer
PDO::ATTR_TIMEOUT Sets the timeout value in seconds for communications with the database. integer
PDO::ATTR_ERRMODE See the Errors and error handling section for more information about this attribute. integer
PDO::ATTR_SERVER_VERSION This is a read only attribute; it will return information about the version of the database server to which PDO is connected. integer
PDO::ATTR_CLIENT_VERSION This is a read only attribute; it will return information about the version of the client libraries that the PDO driver is using. integer
PDO::ATTR_SERVER_INFO This is a read only attribute; it will return some meta information about the database server to which PDO is connected. integer
PDO::ATTR_CURSOR_CASE Force column names to a specific case specified by the PDO::CASE_* constants. integer
PDO::ATTR_CURSOR_NAME Get or set the name to use for a cursor. Most useful when using scrollable cursors and positioned updates. integer
PDO::ATTR_CURSOR  Selects the cursor type. PDO currently supports either PDO::CURSOR_FWDONLY and PDO::CURSOR_SCROLL. Stick with PDO::CURSOR_FWDONLY unless you know that you need a scrollable cursor. integer
PDO::ATTR_DRIVER_NAME Returns the name of the driver. string
PDO::ATTR_ORACLE_NULLS Convert empty strings to SQL NULL values on data fetches. integer
PDO::ATTR_PERSISTENT Request a persistent connection, rather than creating a new connection. See Connections and Connection management for more information on this attribute. integer
PDO::ATTR_FETCH_TABLE_NAMES Prepend the containing table name to each column name returned in the result set. integer
PDO::ATTR_FETCH_CATALOG_NAMES Prepend the containing catalog name to each column name returned in the result set. The catalog name and column name are separated by a decimal (.) character. Support of this attribute is at the driver level; it may not be supported by your driver. integer
PDO::ERRMODE_SILENT  Do not raise an error or exception if an error occurs. integer
PDO::ERRMODE_WARNING  Issue a PHP E_WARNING message if an error occurs integer
PDO::ERRMODE_EXCEPTION  Throw a PDOException if an error occurs. See Errors and error handling for more information about this attribute. integer
PDO::CASE_NATURAL Leave column names as returned by the database driver. integer
PDO::CASE_LOWER Force column names to lower case. integer
PDO::CASE_UPPER  Force column names to upper case. integer
PDO::NULL_NATURAL, PDO::NULL_EMPTY_STRING, PDO::NULL_TO_STRING, PDO::FETCH_ORI_NEXT  Fetch the next row in the result set. integer
PDO::FETCH_ORI_PRIOR Fetch the previous row in the result set. integer
PDO::FETCH_ORI_FIRST Fetch the first row in the result set. integer
PDO::FETCH_ORI_LAST Fetch the last row in the result set. Valid only for scrollable cursors. integer
PDO::FETCH_ORI_ABS  Fetch the requested row by row number from the result set. Valid only for scrollable cursors. integer
PDO::FETCH_ORI_REL Fetch the requested row by relative position from the current position of the cursor in the result set. Valid only for scrollable cursors. integer
PDO::CURSOR_FWDONLY Create a PDOStatement object with a forward-only cursor. This is the default cursor choice, as it is the fastest and most common data access pattern in PHP. integer
PDO::CURSOR_SCROLL Create a PDOStatement object with a scrollable cursor. Pass the PDO::FETCH_ORI_* constants to control the rows fetched from the result set. integer
PDO::ERR_NONE Corresponds to SQLSTATE '00000', meaning that the SQL statement was successfully issued with no errors or warnings. This constant is for your convenience when checking PDO::errorCode() or PDOStatement::errorCode() to determine if an error occurred. You will usually know if this is the case by examining the return code from the method that raised the error condition anyway. integer
PDO::PARAM_EVT_ALLOC Allocation event integer
PDO::PARAM_EVT_FREE Deallocation event integer
PDO::PARAM_EVT_EXEC_PRE Event triggered prior to execution of a prepared statement. integer
PDO::PARAM_EVT_EXEC_POST Event triggered subsequent to execution of a prepared statement. integer
PDO::PARAM_EVT_FETCH_PRE Event triggered prior to fetching a result from a resultset. integer
PDO::PARAM_EVT_FETCH_POST Event triggered subsequent to fetching a result from a resultset. integer
PDO::PARAM_EVT_NORMALIZE Event triggered during bound parameter registration allowing the driver to normalize the parameter name. integer

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

  1. $dbhost = 'localhost';  

  2. $dbname='hr';  

  3. $dbuser = 'root';  

  4. $dbpass = '';  

  5. $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  6. ?>          

PostgreSQL connection

  1. $dbuser = 'postgres';  

  2. $dbpass = 'abc123';  

  3. $host = 'localhost';  

  4. $dbname='postgres';  

  5. $dbh = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);  

  6. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbuser = 'roott';  

  4. $dbpass = '';  

  5. $dbh = new PDO('mysql:host=$dbhost;dbname=hr', $dbuser, $dbpass);  

  6. }catch (PDOException $e){  

  7.  echo "Error!: " . $e->getMessage() . "
    ";  

  8.  die();  

  9.  }  

  10. ?>  

Output :

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

PostgreSQL :

Here the database name is wrong.

  1. $dbuser = 'postgress';  

  2. $dbpass = 'abc123';  

  3. $host = 'localhost';  

  4. $dbname='postgres';  

  5. $dbh = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);  

  6. }catch (PDOException $e){  

  7. echo "Error : " . $e->getMessage() . "
    ";  

  8. die();  

  9. }  

  10. ?>  

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

  1.   

  2. $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);  

  3. // use the connection here  

  4. // Following command close the connection.   

  5. $dbh = null;
      

  6. ?>  

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 :

  1. $dbhost = 'localhost';  

  2. $dbname='hr';  

  3. $dbuser = 'root';  

  4. $dbpass = '';  

  5. $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass, array(  

  6. PDO::ATTR_PERSISTENT => true));     

  7. ?>  

PostgreSQL :

  1. $dbuser = 'postgres';  

  2. $dbpass = 'abc123';  

  3. $host = 'localhost';  

  4. $dbname='postgres';  

  5. $dbh = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass, array(  

  6. PDO::ATTR_PERSISTENT => true));    

  7. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }     

  11. $connec->beginTransaction();  

  12. $result = $connec->exec("INSERT INTO user_details (userid, password, fname, lname, gender, dtob, country, user_rating, emailid) VALUES  

  13. ('abcd123', '123@John', 'John', 'ray', 'M', '1992-06-11', 'USA', '130', 'John123@example-site.com')");  

  14. $connec->commit();  

  15. echo $result;     

  16. ?>   

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.

  1. try{
    $dbuser = 'postgres';  

  2. $dbpass = 'abc123';  

  3. $host = 'localhost';  

  4. $dbname='postgres';  

  5. $connec = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);  

  6. }  

  7. catch (PDOException $e)  

  8. {  

  9. echo "Error : " . $e->getMessage() . "
    ";  

  10. die();  

  11. }   

  12. $query = "SELECT * FROM user_details where genderr='M'";  

  13. $connec->query($query);  

  14. echo $connec->errorCode();  

  15. ?>  

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.

  1. try{  

  2. $dbuser = 'postgres';  

  3. $dbpass = 'abc123';  

  4. $host = 'localhost';  

  5. $dbname='postgres';  

  6. $connec = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);  

  7. }  

  8. catch (PDOException $e)  

  9. {  

  10. echo "Error : " . $e->getMessage() . "
    ";  

  11. die();  

  12. }   

  13. $query = "SELECT * FROM user_details where genderr='M'";  

  14. $connec->query($query);  

  15. echo $connec->errorCode();  

  16. print_r ($connec->errorInfo());    

  17. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. /*  Delete some rows from the allcountry table  */  

  12. $count = $connec->exec("DELETE FROM allcountry WHERE country_id = 'AR'");  

  13. /* Return number of rows */  

  14. echo("Number of deleted rows in allcountry table : ". $count);  

  15. ?>  

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.

  1. try{  

  2. $dbuser = 'postgres';  

  3. $dbpass = 'abc123';  

  4. $host = 'localhost';  

  5. $dbname='postgres';  

  6. $conn = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpass);  

  7. }  

  8. catch (PDOException $e)  

  9. {  

  10. echo "Error : " . $e->getMessage() . "
    ";  

  11. die();  

  12. }  

  13. attributes = array(  

  14. "AUTOCOMMIT", "ERRMODE", "CASE", "CLIENT_VERSION", "CONNECTION_STATUS",  

  15. "ORACLE_NULLS", "PERSISTENT", "PREFETCH", "SERVER_INFO", "SERVER_VERSION",  

  16. "TIMEOUT"  

  17. );  

  18. foreach ($attributes as $val)   

  19. {  

  20. echo "PDO::ATTR_$val: ";  

  21. echo $conn->getAttribute(constant("PDO::ATTR_$val")) . "
    ";  

  22. }  

  23. ?>  

  24.           

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.

  1. print_r(PDO::getAvailableDrivers());  

  2. ?>  

  3.           

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $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', 'John123@example-site.com')");  

  12. $lastRow = $connec->lastInsertId('user_details');  

  13. echo $lastRow ;  

  14. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. /* Execute a prepared statement by passing an array of values */  

  12. $sql = 'SELECT fname, lname, country FROM user_details  

  13.         WHERE country = :country';  

  14. $sth = $connec->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));  

  15. $sth->execute(array(':country' => 'Germany'));  

  16. $c = $sth->fetchAll();  

  17. print_r($c);  

  18. ?>  

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.

  1. try   

  2. {  

  3. $dbhost = 'localhost';  

  4. $dbname='hr';  

  5. $dbuser = 'root';  

  6. $dbpass = '';  

  7. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  8. }catch (PDOException $e) {  

  9. echo "Error : " . $e->getMessage() . "
    ";  

  10. die();  

  11. }  

  12. $sql = $connec->prepare('SELECT fname, lname, country FROM user_details  

  13.        WHERE country = ?');  

  14. $sql->execute(array('Germany'));  

  15. $c = $sql->fetchAll();  

  16. print_r($c);  

  17. ?>  

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.

  1. try {
    $dbhost = 'localhost';  

  2. $dbname='hr';  

  3. $dbuser = 'root';  

  4. $dbpass = '';  

  5. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  6. }catch (PDOException $e) {  

  7. echo "Error : " . $e->getMessage() . "
    ";  

  8. die();  

  9. }  

  10. $sql = 'SELECT fname, lname, country FROM user_details ORDER BY country';  

  11. foreach ($connec->query($sql) as $row)   

  12. {  

  13. print $row['fname'] . " ";  

  14. print $row['lname'] . "-->";  

  15. print $row['country'] . "
    ";  

  16. }  

  17. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $string = 'w3resource';   

  12. echo "Unquoted string : ". $string . "
    ";  

  13. echo "Quoted string : ". $connec->quote($string) . "
    ";  

  14. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }     

  11. $connec->beginTransaction();  

  12. $sth = $connec->exec("DROP TABLE user_detail");  

  13. $connec->rollback();  

  14. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $attributes1 = array( "ERRMODE" );  

  12. foreach ( $attributes1 as $val ) {  

  13. echo "PDO::ATTR_$val: ";  

  14. var_dump ($conn->getAttribute( constant( "PDO::ATTR_$val" ) ));  

  15. }  

  16. $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  

  17. $attributes1 = array( "ERRMODE" );  

  18. foreach ( $attributes1 as $val )   

  19. {  

  20.   echo "PDO::ATTR_$val: ";  

  21.   var_dump ($conn->getAttribute( constant( "PDO::ATTR_$val" ) ));  

  22. }  

  23. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $query = "SELECT fname, lname, dtob, country, emailid FROM user_details where gender = 'M'";  

  12. $stmt = $conn->prepare($query);  

  13. $stmt->execute();  

  14. $stmt->bindColumn('emailid', $email);  

  15. while ( $row = $stmt->fetch( PDO::FETCH_BOUND ) )  

  16. {  

  17. echo "$email"."
    ";  

  18. }  

  19. ?>  

Output :

scott123@example-site.com
palash@example-site.com
John123@example-site.com

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e){  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $user_rating = 100;  

  12. $gender = 'M';  

  13. $sth = $conn->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details  

  14. WHERE user_rating > :user_rating AND gender = :gender');  

  15. $sth->bindParam(':user_rating', $user_rating, PDO::PARAM_INT);  

  16. $sth->bindParam(':gender', $gender, PDO::PARAM_STR, 1);
    $sth->execute();   

  17. ?>  

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

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e){  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $user_rating = 100;  

  12. $gender = 'M';  

  13. $sth = $conn->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details  

  14. WHERE user_rating > ? AND gender = ?');  

  15. $sth->bindParam(1, $user_rating, PDO::PARAM_INT);  

  16. $sth->bindParam(2, $gender, PDO::PARAM_STR, 1);$sth->execute();   

  17. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e){  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $user_rating = 100;  

  12. $gender = 'M';  

  13. $sth = $conn->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details  

  14. WHERE user_rating > :user_rating AND gender = :gender');  

  15. $sth->bindValue(':user_rating', $user_rating, PDO::PARAM_INT);  

  16. $sth->bindValue('gender', $gender, PDO::PARAM_STR, 1);  

  17. print_r($sth->execute());   

  18. ?>  

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e){  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $user_rating = 100;  

  12. $gender = 'M';  

  13. $sth = $conn->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details  

  14. WHERE user_rating > ? AND gender = ?');  

  15. $sth->bindValue(':user_rating', $user_rating, PDO::PARAM_INT);  

  16. $sth->bindValue('gender', $gender, PDO::PARAM_STR, 1);  

  17. print_r($sth->execute());  

  18. ?>  

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 :

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";
    die();  

  9. }  

  10. /* Create a PDOStatement object */  

  11. $stmt = $connec->prepare('SELECT * from user_details');  

  12. /* Create a second PDOStatement object */  

  13. $otherStmt = $connec->prepare("SELECT * from usser_details where gender ='M'");  

  14. /* Execute the first statement */  

  15. $stmt->execute();  

  16. /* Fetch only the first row from the results */  

  17. $stmt->fetch();  

  18. /* The following call to closeCursor() may be required by some drivers */  

  19. $stmt->closeCursor();  

  20. /* Now we can execute the second statement */  

  21. $otherStmt->execute();  

  22. ?>  

 

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.

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e)   

  8. {  

  9. echo "Error : " . $e->getMessage() . "
    ";  

  10. die();  

  11. }  

  12. $query = "select * from user_details";  

  13. $stmt = $connec->prepare( $query );  

  14. $stmt->execute();  

  15. echo "No. of columns: ".$stmt->columnCount();  

  16. ?>  

Output :

No. of columns: 9

PDOStatement::debugDumpParams

Dump an SQL prepared command.

Syntax :
void PDOStatement::debugDumpParams ( void )

Return Value :
No value is returned.

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

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';
    $dbuser = 'root';  

  4. $dbpass = '';  

  5. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  6. }catch (PDOException $e)   

  7. {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $user_rating = 100;  

  12. $gender = 'M';  

  13. $sth = $connec->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details  

  14. WHERE user_rating > :user_rating AND gender = :gender');  

  15. $sth->bindParam(':user_rating', $user_rating, PDO::PARAM_INT);  

  16. $sth->bindParam(':gender', $gender, PDO::PARAM_STR, 12);  

  17. $sth->execute();  

  18. $sth->debugDumpParams();   

  19. ?>  

  20.       

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.

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e)   

  8. {  

  9. echo "Error : " . $e->getMessage() . "
    ";  

  10. die();  

  11. }  

  12. $stmt = $conn->prepare('SELECT fname, tname FROM user_details');  

  13. $stmt->execute();  

  14. echo "PDOStatement::errorCode(): "."
    ";  

  15. print_r ($stmt->errorcode());  

  16. ?>  

  17.       

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().

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e)   

  8. {  

  9. echo "Error : " . $e->getMessage() . "
    ";  

  10. die();  

  11. }  

  12. $stmt = $conn->prepare('SELECT fname, tname FROM user_details');  

  13. $stmt->execute();  

  14. print_r ($stmt->errorInfo());  

  15. ?>  

  16.       

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.

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e){  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $user_rating = 100;  

  12. $gender = 'M';  

  13. $sth = $connec->prepare('SELECT fname, lname, dtob, country, emailid FROM user_details  

  14. WHERE user_rating > :user_rating AND gender = :gender');  

  15. $sth->bindParam(':user_rating', $user_rating, PDO::PARAM_INT);  

  16. $sth->bindParam(':gender', $gender, PDO::PARAM_STR, 12);  

  17. $sth->execute();  

  18. while ( $row = $sth->fetch( PDO::FETCH_ASSOC ) )  

  19. {  

  20. echo "$row[fname]"." "."$row[lname]";  

  21. }   

  22. ?>  

  23.       

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.

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $sth = $conn->prepare("SELECT fname, lname FROM user_details");  

  12. $sth->execute();  

  13. /* Exercise PDOStatement::fetch styles */  

  14. echo "PDO::FETCH_ASSOC: "."
    ";  

  15. echo "Return next row as an array indexed by column name"."
    ";  

  16. $result = $sth->fetch(PDO::FETCH_ASSOC);  

  17. print_r($result);  

  18. echo("
    ");  

  19. echo "PDO::FETCH_BOTH: "."
    ";  

  20. echo "Return next row as an array indexed by both column name and number"."
    ";  

  21. $result = $sth->fetch(PDO::FETCH_BOTH);  

  22. print_r($result);  

  23. echo("
    ");  

  24. echo "PDO::FETCH_LAZY: "."
    ";  

  25. echo "Return next row as an anonymous object with column names as properties"."
    ";  

  26. $result = $sth->fetch(PDO::FETCH_LAZY);  

  27. print_r($result);  

  28. echo("
    ");  

  29. echo "PDO::FETCH_OBJ: "."
    ";  

  30. echo "Return next row as an anonymous object with column names as properties"."
    ";  

  31. $result = $sth->fetch(PDO::FETCH_OBJ);  

  32. echo $result->fname;  

  33. ?>  

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

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e)   

  8. {  

  9. echo "Error : " . $e->getMessage() . "
    ";  

  10. die();  

  11. }  

  12. $sth = $connec->prepare("SELECT fname, lname FROM user_details");  

  13. $sth->execute();  

  14. /* Fetch all of the remaining rows in the result set */  

  15. print("Fetch all of the remaining rows in the result set: ");  

  16. $result = $sth->fetchAll();  

  17. print_r($result);   

  18. ?>  

  19.       

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.

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e)   

  8. {echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $stmt = $connec->query("select * from user_details where gender='M';");  

  12. while ( $result = $stmt->fetchColumn(1))   

  13. {  

  14. echo $result . "
    ";   

  15. }  

  16. ?>  

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.

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e)   

  8. {  

  9. echo "Error : " . $e->getMessage() . "
    ";  

  10. die();  

  11. }  

  12. $stmt = $connec->query( "select * from user_details where gender='M';" );  

  13. $result = $stmt->fetchObject();  

  14. echo $result->fname;  

  15. ?>  

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.

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $select = $connec->query("select fname, lname from user_details where gender='M';");  

  12. $meta = $select->getColumnMeta(0);  

  13. var_dump($meta)   

  14. ?>  

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.

view plaincopy to clipboardprint?

  1. $dbhost = 'localhost';  

  2. $dbname='hr';  

  3. $dbuser = 'root';  

  4. $dbpass = '';  

  5. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  6. }catch (PDOException $e)   

  7. {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $query1 = "select region_name from regions where region_id>2;";  

  12. $query2 = "select fname from user_details where gender='M';";  

  13. $stmt = $connec->query( $query1 . $query2 );  

  14. $rowset1 = $stmt->fetchAll();  

  15. $stmt->nextRowset();  

  16. $rowset2 = $stmt->fetchAll();  

  17. var_dump($rowset1 );  

  18. echo "
    ";  

  19. 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.

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $connec = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e)   

  8. {  

  9. echo "Error : " . $e->getMessage() . "
    ";  

  10. die();  

  11. }  

  12. /* Delete all rows from the alluser table * 

  13. $del = $connec->prepare('DELETE FROM alluser'); 

  14. $del->execute(); 

  15. /* Return number of rows that were deleted */  

  16. $count = $del->rowCount();  

  17. echo "Deleted ".$count." number of rows ";   

  18. ?>  

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.

view plaincopy to clipboardprint?

  1. try {  

  2. $dbhost = 'localhost';  

  3. $dbname='hr';  

  4. $dbuser = 'root';  

  5. $dbpass = '';  

  6. $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  

  7. }catch (PDOException $e) {  

  8. echo "Error : " . $e->getMessage() . "
    ";  

  9. die();  

  10. }  

  11. $stmt1 = $conn->query( "select * from user_details where gender='M'" );
      

  12. while ( $row = $stmt1->fetch())  

  13. {  

  14. echo($row['fname'] . "
    ");  

  15. }  

  16. echo "
    "."PDO::FETCH_ASSOC -------------"."
    ";  

  17. $stmt = $conn->query( "select * from user_details where gender='M'" );  

  18. $stmt->setFetchMode(PDO::FETCH_ASSOC);  

  19. $result = $stmt->fetch();  

  20. print_r( $result );  

  21. echo "
    "."PDO::FETCH_NUM -------------"."
    ";  

  22. $stmt = $conn->query( "select * from user_details where gender='M'" );  

  23. $stmt->setFetchMode(PDO::FETCH_NUM);  

  24. $result = $stmt->fetch();  

  25. print_r( $result );  

  26. echo "
    "."PDO::FETCH_BOTH -------------"."
    ";  

  27. $stmt = $conn->query( "select * from user_details where gender='M'" );  

  28. $stmt->setFetchMode(PDO::FETCH_BOTH);  

  29. $result = $stmt->fetch();  

  30. print_r( $result );  

  31. echo "
    "."PDO::FETCH_LAZY -------------"."
    ";  

  32. $stmt = $conn->query( "select * from user_details where gender='M'" );  

  33. $stmt->setFetchMode(PDO::FETCH_LAZY);  

  34. $result = $stmt->fetch();  

  35. print_r( $result );  

  36. echo "
    "."PDO::FETCH_OBJ -------------"."
    ";  

  37. $stmt = $conn->query( "select * from user_details where gender='M'" );  

  38. $stmt->setFetchMode(PDO::FETCH_OBJ);  

  39. $result = $stmt->fetch();  

  40. print_r( $result );  

  41. ?>  

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] => scott123@example-site.com ) 
PDO::FETCH_NUM -------------
Array ( [0] => scott123 [1] => 123@sco [2] => Scott [3] => Rayy [4] => M [5] => 1990-05-15 [6] => USA [7] => 100 [8] => scott123@example-site.com ) 
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] => scott123@example-site.com [8] => scott123@example-site.com ) 
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] => scott123@example-site.com ) 
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] => scott123@example-site.com )