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 = 'david@example.com';

  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] => david@example.com
            [2] => david@example.com
            [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?

Thats supposed to happen when you use the default `fetchAll()`. It allows you to use the array like `$results[0]` or `$results['staff_id;]` Use `fetchAll(PDO::FETCH_ASSOC)` for just the `$results['staff_id;]` version and `fetchAll(PDO::FETCH_NUM)` for just the `$results[0]` The default you see in yuur result is because the default is `fetchAll(PDO::FETCH_BOTH)` — RiggsFolly, Aug 16 '15 at 15:29
look at this for inspiration http://stackoverflow.com/questions/31906248/user-inputs-clean-and-sanitize-before-sending-to-db/31996862#31996862 — Maytham Fahmi, Aug 16 '15 at 15:30

score 5 · Accepted Answer · answered Aug 16 '15 at 15:26

5

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.

answered Aug 16 '15 at 15:26

jeroen

91,079
21
114
132

1

Of course it is. Thanks! Nice and simple answer - amazing what a second pair of eyes does! That was doing my head in – Blackpool Towr Aug 16 '15 at 15:36

score 3 · Answer 2 · answered Aug 16 '15 at 15:26

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);

score 2 · Answer 3 · answered Aug 16 '15 at 15:28

$query->fetchAll(); defaults to the PDO::FETCH_BOTH fetch style, which returns both the numbered array and the named array.

This should get you just the named array:

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

Refs:

PHP Man page for fetch_all()

PHP Man page for fetch()

MySQL returns data twice

3 Answers3