PHP - PDO fetch resultset with column as index and column as value

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

Of course , but will it reduce the performance hits that i get currently with foreach(). — Maxx, Feb 22 '14 at 10:33
well there are already 4 outer loops in the program and this array calc happens in the inner most loop — Maxx, Feb 22 '14 at 10:45
Theres nothing wrong with this method. I was just worried that adding another loop inside 4 outer loops might worsen things , so i was looking for some efficient way which i wasn't aware of :-) — Maxx, Feb 22 '14 at 11:01
Don't you understand that to get some rows from database you *have* to loop over them wither way? Even if it were disguised in some fancy API method, it cannot return you an array by just magic - there would be a loop inside anyway. — Your Common Sense, Feb 22 '14 at 11:03
Of course i do. That to get some rows from the db we need to loop over them either way. Its common sense afterall. :-) — Maxx, Feb 22 '14 at 11:11
So, you can conclude that as you cannot avoid a loop, there is nothing to worry about. — Your Common Sense, Feb 22 '14 at 11:42
yeah if you put it that way, you're right. There's no avoiding the inevitable — Maxx, Feb 22 '14 at 12:17

score 7 · Answer 1 · edited Oct 25 '20 at 19:13

7

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.

edited Oct 25 '20 at 19:13

Your Common Sense

156,878
40
214
345

answered Jul 11 '14 at 15:14

Pere Hernández

127
1
3

3

Also, `PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC` works with more columns. See [this edit](https://stackoverflow.com/questions/1387563/php-pdostatement-fetch-a-row-as-the-first-column-as-the-key-of-an-array/12026941#answer-12026941). – showdev Dec 13 '17 at 19:04

score 4 · Accepted Answer · answered Feb 22 '14 at 07:25

4

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'];
}

answered Feb 22 '14 at 07:25

Barmar

741,623
53
500
612

thanks for your response, i had this feeling that there wasn't any built in method to do this. But then again i thought that there might be some of these flags like like PDO::FETCH_NAMED or PDO::FETCH_NUM which can be used to achieve this which i wasn't aware of. So i posted my question here. – Maxx Feb 22 '14 at 10:23
And as the main event loop of my program already have 4 loops , which increased the complexity to O(N*N*N*N), so i didn't wanted another loop to further increase the complexity :-) – Maxx Feb 22 '14 at 10:26
Make sure all those nested loops are really necessary. If you're doing nested queries, they can often be replaced with a single query that uses a `JOIN`, and logic in the `fetch` loop that combines related rows. – Barmar Feb 22 '14 at 10:29
No i haven't used nested queries. The program calculates subproperties of 134 different objects and there are around 5000 subproperties of each objects, the query runs within each subproperties which also has another set of minor properties to calculate. – Maxx Feb 22 '14 at 10:43
Anyways i'm using your method as this seems most logical for the time being. – Maxx Feb 22 '14 at 10:44
@Barmar : how does the performance varies with `foreach ($sql_stmt as $row) {` becuase it stores the values in array internally ? – logan Feb 22 '14 at 10:52
They should be similar. I don't think `$sql_stmt` stores the entire result in an array, it's an iterator that fetches each row on demand. Just don't use `fetchAll`, as that _does_ fetch the entire result into memory, and then loops over it a second time to process it. – Barmar Feb 22 '14 at 10:56

score -1 · Answer 3 · answered Feb 22 '14 at 07:39

Try foreach ($sql_stmt as $row)... Its easy to handle all your result sets...

Change the database name , user id , table name etc., according to your system

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbConnection= new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

$sql_stmt = $dbConnection->prepare($sql_query="select date,price from table;");

$sql_stmt->execute();
foreach ($sql_stmt as $row) {
$date_col=$row['date']; 
$price_col=$row['price']; 
print $date_col;
print $price_col;
}

If you want to store value in array then use, like below in same foreach loop (declare and increment $i)

$date_col[$i]=$row['date']; 
$price_col[$i]=$row['price'];

Thanks for your response. Yes this is not what i was looking for, cause i already mentioned that i can easily do this, but i was looking for some efficient way to map two column as index and value. — Maxx, Feb 22 '14 at 10:18

PHP - PDO fetch resultset with column as index and column as value

3 Answers3

Related