PHP - PDO fetch resultset with column as index and column as value [duplicate]

Question

Hi i have a table with the following structure

+-------------+------+
| date        | price|
+-------------+------+
| 2014-02-19  |   34 |
| 2014-02-20  |   30 |
| 2014-02-21  |   28 |
+-------------+------+

At present PDO::FETCH_ASSOC returns an associative array with format like this

array(
  0=> array(
    "date" =>  2014-02-19 ,
    "price" => 34
),
 1=> array(
    "date" =>  2014-02-20 ,
    "price" => 30
),
 2=> array(
    "date" =>  2014-02-21 , 
    "price"=> 28
 )

)

This is alright by the way, but i was looking for a way to return the values as key => value pair with key is date and value is price. So that i can quickly access a price value using the date as the key and thus reduce the amount of computation by a lot, cause i have to deal with over several millions rows, and accessing each value in a loop is only causing the program to slow down much further.

So here is what i was looking for PDO::fetchALL() to return

array(
"2014-02-19" => 34,
"2014-02-20" => 30,
"2014-02-21" => 28
)

I mean i can easily build this using a foreach loop, but its not a possibility in my situation since its incurring huge performance drops. So if any one could point me in the right direction it would be really helpful. I will appreciate any sort of helps or tips.

Thanks, Maxx

Answer 1

I think it can be done with fetch options.

$sth = $dbh->prepare("SELECT date, price FROM some_table");
$sth->execute();


$sth->fetchAll(PDO::FETCH_KEY_PAIR);

It only works if the fetched data are a pair of values.

Answer 2

I don't think there's anything built-in that will do that. You can do it by using a normal fetch() loop:

$results = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $results[$row['date']] = $row['price'];
}