Using value of a column as index in results using PDO

Question

I have an SQL table called 'brands' with the columns id, name, url. In that table I have this data set:

1, Solidfloor, solidfloor;
2, Quickstep, quickstep;
4, Cleanfloor, cleanfloor;
5, Blue Dolphin, blue-dolphin;
6, Krono, krono;
8, Meister, meister;

I'm fetching them all right now and I get a nice array in return, but, I need the index of the arrays to not be an incremented number, but the id of that particular row. I could of course loop through the result set, but is there a cleaner way of doing this?

Answer 1

Although PDO::FETCH_UNIQUE description in PHP manual is quite unclear, but in fact it's exact parameter you actually need.

$data = $pdo->query('SELECT * FROM table')->fetchAll(PDO::FETCH_UNIQUE);

is giving you an array indexed by the field listed in SELECT clause first (when * is used then first field in the table definition, which should be id in your case).

Note that by default using just PDO::FETCH_UNIQUE will give you resulting rows with doubled values. You can either add preferred row mode to this call or - better, set it once for all PDO calls in constructor or via setAttribute(). The output below is shown for PDO::FETCH_ASSOC set as default fetch mode.

  1 => array (
    'name' => 'Solidfloor',
    'url' => 'solidfloor',
  ),
  2 => array (
    'name' => 'Quickstep',
    'url' => 'quickstep',
  ),
  4 => array (
    'name' => 'Cleanfloor',
    'url' => 'cleanfloor',
  ),
)