MySQL returns data twice

Question

I have the following code, simplified heavily while trying to get to the bottom of this:

<?php
  $db_host   = $_ENV['DATABASE_SERVER'];
  $db_pass   = 'dummypassword';
  $db_user   = 'user';
  $db_name   = 'database';
  $db_char   = 'utf8';

  $db = new PDO("mysql:host=$db_host;dbname=$db_name;charset=$db_char", $db_user, $db_pass);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $email = '[email protected]';

  if (!empty(trim($email)))
    {
      $sql = 'SELECT * FROM staff';
      $query = $db->prepare($sql);
      $query->execute();
      $results = $query->fetchAll();

      echo '<pre>';
      print_r($results);
      echo '</pre>';
    }
?>

When I run this, the query works fine and I get no errors. What I do get however is:

Array
(
    [0] => Array
        (
            [staff_id] => 1
            [0] => 1
            [staff_org] => 1
            [1] => 1
            [staff_email] => [email protected]
            [2] => [email protected]
            [staff_password] => hashyhashy
            [3] => hashyhashy
            [staff_first_name] => David
            [4] => David
            [staff_start_date] => 2014-03-17
            [7] => 2014-03-17
            [staff_leave_date] => 
            [8] => 
        )
)

Personally I don't remember ever seeing it return the data twice like this (i.e. with a named key, and then again without). My question is this... have I lost my mind and this always how results from MySQL have been returned? Or have I done something to make it do this?

Answer 1

You are getting both a numeric zero-indexed key and an associative key. If you need just the associative array, you can do:

$results = $query->fetchAll(PDO::FETCH_ASSOC);

and if you just want numeric keys, you do:

$results = $query->fetchAll(PDO::FETCH_NUM);

Check the manual on fetch() for more details.

Answer 2

You can pass the optional parameter fetch_style to the fetchAll() method. The default value of this parameter is PDO::FETCH_BOTH so that the result array contains the numeric and the associative keys.

If you only want to get the associative array keys, you have to change

$query->fetchAll();

to

$query->fetchAll(PDO::FETCH_ASSOC);