Taking advantage of PDO’s fetch modes

PHP PDO

Objectives

I want to:

  • Extract information from the database and be able to loop through records without having to do too much processing beforehand.

Prerequisites

  • A functional LAMP/WAMP stack with PDO enabled

PDO should be enabled by default (if PHP > 5.1.0).

Our base class

The main goal of this article is to explore fetch modes but we still need to connect to the database and be able to carry out simple tasks, so we’re going to set up a little class with the bare essentials. We also made a github gist of the code covered here.

class db
{
 /**
  * @var PDO $connect Our object taking care
  * of the db connection
  */
  protected $connect;
  /**
  * @var PDOStatement $declaration holds the prepared statement
  * (the query)
  */
  protected $declaration;
   
 /**
  * @param string $db the database name to connect to
  * @param string $ip IP address of the database server
  * @param string $login username
  * @param string $pass password
  * @param integer $port port
  */
  function __construct($db, $ip, $login, $pass, $port = 3306)
  {
    try {
      $this->connect = new PDO(
        'mysql:host=' . $ip .
        ';port=' .  $port .
        ';dbname='; . $db .
        ';charset=utf8', $login, $pass
      );
    } catch (PDOException $pe) {
      echo "Unable to connect to the database.";
      die();
    }
   
    //Have PDO throw a PDO Exception in case of error
    $this->connect->setAttribute(
      PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //Leave column names as returned by the database driver
    $this->connect->setAttribute(
      PDO::ATTR_CASE, PDO::CASE_NATURAL);
     
    if (!$this->connect) {
      echo "Unable to connect to the database";
      die();
    }
  }
}
Don’t forget to set the charset when initializing PDO. Without a PDO charset matching the database’s, non-supported characters won’t be rendered properly (the infamous ‘�’ character will appear).

Now that our base class is set, we add a method to manage errors.

/**
 * Display errors properly
 *
 * @param PDOException $e The thrown exception
 * @param string $sql_query The query
 * @param Array $values Values that were passed along 
 * with the query
 * @param string $type Is equal to values 
 * if values have been passed 
*/
private function errorFormat($e, $sql_query, $values, $type)
{
  echo "
/**
Invalid prepared query :
" . $sql_query . "
"; echo "Transmitted values :
"; if ($type == 'values') { print_r($values); } else { echo 'NULL'; } echo "
Message :
"; var_dump($e->getMessage()); echo "
Stack trace :
"; echo $e->getTraceAsString(); echo "
**/
"; }

Our next method handles the execution of queries and handles the formatting of errors if need be.

/** 
* Execute queries 
* 
* @param string $sql_query The SQL query 
* @param Array $values Values that are passed along
* with the query 
* @return boolean $ok 
*/ 
protected function queryDB($sql_query, $values = null) 
{ 
  $ok = true; 
  $this->declaration = $this->connect->prepare($sql_query);

  if ($values != null) {
    if (is_array($values)) {
      try { 
        $this->declaration->execute($values); 
      } catch (PDOException $e) { 
        $this->errorFormat($e, $sql_query, $values, 'values'); 
        $ok = false; 
      }
    } else { 
      echo "Incorrect settings"; 
      $ok = false; 
    }
  } else {
    try {
      $this->declaration->execute(); 
    } catch (PDOException $e) { 
      $this->errorFormat($e, $sql_query, $values, 'NULL'); 
      $ok = false; 
    }
  } 
  return $ok; 
}

Let’s finish with the method we’ll spend some time dissecting, our select method:

/** 
* Select query 
* 
* @param string $sql_query The SQL query 
* @param Array $values Values that are passed along
* with the query 
* @param string $type format the result 
* @return mixed Query result 
*/ 
public function select($sql_query, $values = null, $type = "") 
{
  $this->queryDB($sql_query, $values); 
  switch ($type) { 
    case "" : 
      return $this->declaration->fetchAll(
       PDO::FETCH_ASSOC
      ); 
    break; 
    case "KEYS" : 
      return $this->declaration->fetchAll(
       PDO::FETCH_KEY_PAIR
      ); 
    break; 
    case "GROUP" : 
      return $this->declaration->fetchAll(
       PDO::FETCH_GROUP | PDO::FETCH_ASSOC
      ); 
    break; 
    case "COLUMN" : 
      return $this->declaration->fetchAll(
       PDO::FETCH_COLUMN
      ); 
    break; 
    case "GROUPCOL" : 
      return $this->declaration->fetchAll(
       PDO::FETCH_COLUMN | PDO::FETCH_GROUP
      ); 
    break; 
    case "OBJ" : 
      return $this->declaration->fetchAll(
       PDO::FETCH_CLASS
      ); 
    break; 
    case "GROUPOBJ" : 
      return $this->declaration->fetchAll(
       PDO::FETCH_GROUP | PDO::FETCH_CLASS
      ); 
    break; 
  }
  return false; 
}

There are plenty of fetch mode combinations you can try besides these, but I found that these will cover most of our needs.

Fetch modes

FETCH_ASSOC

We start with the classic FETCH_ASSOC, which as its name indicates returns our result set in the form of an associative array:

$result = $db->select("select * from languages");
print_r($result);

//Prints the following:
Array  
(  
  [0] => Array  
  (  
    [language_id] => 1  
    [code] => eng  
    [language_name] => English  
    [language_family] => Indo-European  
    [ISO_639_1] => en  
  )

  [1] => Array  
  (  
    [language_id] => 2  
    [code] => abk  
    [language_name] => Abkhaz  
    [language_family] => Northwest Caucasian  
    [ISO_639_1] => ab  
  )

  [2] => Array  
  (  
    [language_id] => 3  
    [code] => ace  
    [language_name] => Aceh (Achenese)  
    [language_family] =>  
    [ISO_639_1] =>  
  )
...  
)

FETCH_KEY_PAIR

This fetch mode also returns an associative array but is designed to work exclusively with two columns. It’ll throw an exception if there are more than two columns in the prepared statement.

( ! ) Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000]: General error: PDO::FETCH_KEY_PAIR fetch mode requires the result set to contain extactly 2 columns.’ in db.php on line 118
( ! ) PDOException: SQLSTATE[HY000]: General error: PDO::FETCH_KEY_PAIR fetch mode requires the result set to contain extactly 2 columns. in db.php on line 118

In our test query we get our language codes with their matching IDs.

$result = $db->select(
  "select code, language_id from languages", null, "KEYS"
);
print_r($result);

//Prints the following:
Array
(
  [eng] => 1
  [abk] => 2
  [ace] => 3
  [ach] => 4
  [ady] => 5
  [aar] => 6
  [afh] => 7
  [afr] => 8
  [afa] => 9
  [ain] => 10
  [aka] => 11
...
)

Very neat when we want to match a string value with its ID.

GROUP & ASSOC

The FETCH_GROUP fetch mode can pair itself with other modes to enable groupings. In this example we group languages by language family by pairing FETCH_GROUP with FETCH_ASSOC:

$result = $db->select(
  "select code, language_id from languages", null, "GROUP"
);
print_r($result);

//Prints the following:
Array  
(  
  [Indo-European] => Array  
    (  
      [0] => Array  
        (  
          [code] => eng  
          [language_id] => 1  
        )

      [1] => Array  
        (  
          [code] => afr  
          [language_id] => 8  
        )

      [2] => Array  
        (  
          [code] => alb  
          [language_id] => 13  
        )  
...
        )  
    [Northwest Caucasian] => Array  
    (  
      [0] => Array  
        (  
          [code] => abk  
          [language_id] => 2  
        )

    )  
...
)

COLUMN

This fetch mode grabs the first column it finds in a result set and produces a flat array.

$result = $db->select(
  'select distinct language_family 
   from languages 
   where language_family', null, "COLUMN"
);
print_r($result);

//Prints the following:
Array
(
  [0] => Indo-European
  [1] => Northwest Caucasian
  [2] => Afro-Asiatic
  [3] => Niger–Congo
  [4] => Northeast Caucasian
  [5] => Aymaran
...
)

COLUMN & GROUP

Now that we’ve covered both FETCH_COLUMN and FETCH_GROUP, let’s try and combine them in a result set that groups language codes according to their first letter:

print_r($db->select(
   'select substr(ISO_639_1,1,1) as iso, language_name
   from languages 
   where ISO_639_1!=""
   order by ISO_639_1', null, "GROUPCOL")
);
print_r($result);

//Prints the following:
Array  
(  
  [a] => Array  
  (  
    [0] => Afar  
    [1] => Abkhaz  
    [2] => Avestan  
    [3] => Afrikaans  
    [4] => Akan  
    [5] => Amharic  
    [6] => Aragonese  
    [7] => Arabic  
    [8] => Assamese  
    [9] => Avar (Avaric)  
    [10] => Aymara  
    [11] => Azerbaijani  
  )

  [b] => Array  
  (  
    [0] => Bashkir  
    [1] => Belarusan (Belarusian)  
    [2] => Bulgarian  
    [3] => Bihari  
    [4] => Bislama  
    [5] => Bamanankan (Bambara)  
    [6] => Bengali  
    [7] => Tibetan  
    [8] => Breton  
    [9] => Bosnian  
  )  
)

FETCH_CLASS

Let’s wrap up this quick tour of fetch modes by covering how to get PDO to return our result set in the form of objects. The FETCH_CLASS mode will return an array of stdClass instances, which will allow us to use the object operator -> to access the object’s properties (our table columns). Here’s what it looks like:

$result = $db->select("select * from languages", null, "OBJ");
print_r($result);

//Prints the following:
Array
(
  [0] => stdClass Object
  (
    [language_id] => 1
    [code] => eng
    [language_name] => English
    [language_family] => Indo-European
    [ISO_639_1] => en
  )

  [1] => stdClass Object
  (
    [language_id] => 2
    [code] => abk
    [language_name] => Abkhaz
    [language_family] => Northwest Caucasian
    [ISO_639_1] => ab
  )
)

Conclusion

It’s pretty easy to overlook just how useful PDO and its fetch modes can be out of the box, but upon closer inspection, we realize pretty quickly how it can help us parse through our data by delivering result sets that are almost ready to use, with the extra perk of being able to switch modes from one query to the next.

Further reading