PHP PDOStatement: Fetch A Row, as the First Column as the Key of an Array

Question

I am using PDOStatement to query the database. Whenever I get a returned row, I want it to be fetched into an array, with the $row[0] as the key, and the subsequent elements in the row as the values.

I can, of course, write a combination of foreach loops and if conditionals to do the job, such as the below:

private static function GetMySQLResult($dbname, $sqlString) {


  $dbh =  self::ConstructPDOObject($dbname);
   $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $result=array();
   foreach ($dbh->query($sqlString) as $row)
   {
    $result[$row[0]][]=$row[1];  // the simplest case for 2 columns, should add more to handle more columns

   }

   return $result;

}

but I am looking for an existing method; is there such a method already exist?

Answer 1

Some tips, you need to pass the right fetch style to the PDOStatement->fetch() method so that you don't end up with double data (numeric and textual column names). Like $row[0] and $row['id'] which both contain the same value when you use PDO::FETCH_BOTH.

$result = $dbh->query($sqlString);
while ($row = $result->fetch(PDO::FETCH_NUM)) {
...

As for your question, you will have to fetch all the results and then create an array with the $row['id'] as the key and the result row as the value - just like you are doing. I built an entire ORM library around PDO and I could never find anything to do this automatically.

$result = $dbh->query($sqlString);

$results = array();
while ($row = $result->fetch(PDO::FETCH_NUM)) {
    $results[$row[0]] = $row;
}

return $results;