PHP - Introduction To PDO Part 2

In part 1, I explained how to connect to a MySQL database server using the PDO extension. In this tutorial, I explain more about the PDOStatement class and how it's used to fetch data.

If you haven't already done so, I would suggest reading Introduction To PDO Part 1 , which explains how to connect to a MySQL server and issue SQL statements using the query() method.

Part 2

When an SQL statement is issued using either the query/prepare method of the PDO class, an instance of PDOStatement is returned. This object represents a prepared statement and holds an associated result set if the SQL statement issued was a SELECT statement. You can then use methods such as fetch(), fetchAll(), fetchColumn() and fetchObject to return data from the result set. Each method mentioned returns data in a different way. For example, the fetch() method will return the next row data from the result set, while fetchAll() will return an array of all rows. Both the fetch() and fetchAll() methods allow you to specify in which format the data should be returned. For example, by default, both methods will return an array indexed by both column name and number, resulting in duplicate data. A sample result is shown below for clarity.

1. Array  
2. (  
3.     [user_id] => 1  
4.     [0] => 1  
5.     [first_name] => John  
6.     [1] => John  
7.     [last_name] => Smith  
8.     [2] => Smith  
9. ) 

Notice, how the array contains duplicate data. This is because the default behavior is to return an array indexed by both column name and number. This behavior can be changed by supplying a fetch style argument to the fetch() method. The code below shows how to change the default behavior so that an array indexed by column name (Associative array) is returned without indexed numbers.

1. <?php  
2.     $pdo = new PDO("mysql:dbname=dbname;host=127.0.0.1", "root", "");     
3.     $stm = $pdo->query("SELECT * FROM users");     
4.     $row = $stm->fetch(PDO::FETCH_ASSOC);     
5.          
6.     print_r($row);     
7. ?>   

PDO::FETCH_ASSOC is a PDO constant, which specifies the fetch style of the data to return. Other FETCH_* style constants exists, which allow you to return data in different ways. For example instead of returning an array, you could return a anonymous object using the FETCH_OBJ constant, where the object properties map to column names.

Another useful constant is the FETCH_CLASS constant, which allows you to specify your own class mapping. Class properties must be declared public for mapping to be successful. The code below shows how to map the User class to the result.

1. <?php     
2.  
3.     class User {  
4.          public $first_name;  
5.          public $last_name;  
6.     }  
7.       
8.     $pdo = new PDO("mysql:dbname=dbname;host=127.0.0.1", "root", "");     
9.     $stm = $pdo->query("SELECT * FROM users");  
10.     $stm->setFetchMode(PDO::FETCH_CLASS, "User");       
11.     $user = $stm->fetch();     
12.          
13.     print_r($user);     
14. ?> 

User defined class property names must match the column names in the result set. The setFetchMode() changes the default behavior of the fetch style. Any calls to either fetch or fetchAll methods will use the fetch style specified.

The PDOStatement class has a rowCount() method which returns the number of rows affected when either an INSERT,UPDATE or DELETE SQL statement is issued. Most databases don't return the number of affected rows when a SELECT statement is issued. To count the number of rows affected, you can issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use fetchColumn() to retrieve the number of rows that will be returned.