Most efficient way to populate array from database values?

Question

If I wanted to get a list of product_ids with a certain brand. I would do this:

$id_list = array();
$qry = 'SELECT product_id FROM products WHERE product_brand = :brand';
$STH = $this->pdo->prepare($qry);
$STH->execute(array("brand" => $brand));
$STH->setFetchMode(PDO::FETCH_ASSOC);
while($row = $STH->fetch())
{
    $id_list[] = $row['product_id'];
}

Is there a faster more efficient way? It seems like if I am only selecting 1 column there should be a better approach to selecting/inserting that into an array.

@Col. Shrapnel, yes. I have different tables for different types of products. So to generate a list of all product_ids with a certain condition I need to run something like the above on each table. (they all are tied to a base table so the ID's don't overlap). So am searching each product type table for products that meet the condition, and storing the results in an array. Then once I have an array of all the matching product ids. I will loop through them and pass them into my product class that will list them on the page. — JD Isaacks
– JD Isaacks, Commented Sep 30, 2010 at 20:31
.. So since I am going to be making the above call multiple times (on different tables) I wanted to make it as efficient as possible. — JD Isaacks
– JD Isaacks, Commented Sep 30, 2010 at 20:31
That is what you are doing wrong. A real optimization would be to avoid such multiple selects and let database do this job. — Your Common Sense
– Your Common Sense, Commented Sep 30, 2010 at 20:35
Ah. the folly of the aged, barking a the youngins for not trying to find things out, and barking at the youngins when they try to find out the 'wrong' thing, making acquiring knowledge a happy lottery of abuse, with the occasional winning ticket called 'grumpily mumbling you already knew that 20 years ago, and it should be basic knowledge for anyone'. — Wrikken
– Wrikken, Commented Sep 30, 2010 at 21:56

Wrikken · Accepted Answer · 2010-09-30 20:28:06Z

4

$STH->setFetchMode(PDO::FETCH_COLUMN,0);
$id_list = $STH->fetchAll();

Is it really faster? Local benchmarK:

$ cat 1.php 
<?php
$d = new PDO('mysql:localhost');
$qry = 'SELECT SQL_NO_CACHE bar FROM test.foo'; //for completeness sake: foo has 400 rows
$stmt = $d->query($qry);
$stmt->setFetchMode(PDO::FETCH_COLUMN,0);
$check = $stmt->fetchAll();
?>
$ cat 2.php 
<?php
$d = new PDO('mysql:localhost');
$qry = 'SELECT SQL_NO_CACHE bar FROM test.foo'; //for completeness sake: foo has 400 rows
$stmt = $d->query($qry);
$check = array();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
while($row = $stmt->fetch()){
        $check[] = $row['bar'];
}
?>
$ time (for i in {1..100}; do php 1.php; done;)

real    0m4.507s
user    0m2.392s
sys     0m1.288s
$ time (for i in {1..100}; do php 2.php; done;)

real    0m6.830s
user    0m3.352s
sys     0m2.328s

.. so, at least this script difference, on my server, is faster...

edited Sep 30, 2010 at 20:28

answered Sep 30, 2010 at 20:16

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

JD Isaacks Over a year ago

+1 Awesome, thanks!.. I knew there had to be something... Question tho, is this actually faster or just require less typing?

Wrikken Over a year ago

Hmm, less arrays created, I'd think it's negligible, but I'll have a benchmark in a few minutes.

Your Common Sense Over a year ago

oh no. Wrikken and his trained zillion iterations tests again.

Wrikken Over a year ago

Zillion? As if my computer didn't have anything better to do. And AFAIK this is only my second I posted on SO. Feel free to suffer horrible despair because the actual benchmarking, or gloat mercilessly because someone has yet again taken the bait, dear Shrappy.

Wrikken Over a year ago

No M. Collateral Damage, the question was 'is this faster', to which the answer is (under certain conditions) 'yes'. The question was not 'Will it make my application much faster?', to which you, I and probably the OP know the answer: not really enough to care. The question certainly was not 'Do I even select the proper records' or 'What's the biggest speedgain I can get in my application'. Also in real life, there are other sites & applications then your average home demolishment blog or news about the tramp next door in which selecting hundreds of rows is a valid practice. /hungry

|

Collectives™ on Stack Overflow

Most efficient way to populate array from database values?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related