Reusing a PDO Query block in another area best practices

Question

I'm just transitioning over to PDO from mysql_

I have been pointed in the direction of some helpful tutorials, but there is one issue I haven't seen raised and I wanted to check I am doing it in the 'correct way'

I want to reuse the same values that I am dropping into an array in two different areas (both different PHP code blocks) within one page. Both are styled differently depending on media queries (hidden/not hidden etc.)

At the moment I am running a query like this:

<?php
$data = $pdo->query("SELECT * FROM sitelinks WHERE `show` = 'yes' ORDER BY `Order` ASC")->fetchAll();
foreach ($data as $links)
{
echo    "\n<li class=\"linkitem\"><a href=\"{$links['URL']}\">{$links['Text']}</a></li>";
    }
    ?>

So my understanding is that I should then be able to load and loop through the $links variable somewhere else on the page. I am doing that like this:

<?php
foreach ($data as $links)
{
echo    "\n<li class=\"desktoplinkitem\"><a href=\"{$links['URL']}\">{$links['Text']}</a></li>";
}
?>

Is that correct? It's working, but seems a little crazy to use ($data as $links) again. Following on, really stupid question but why does the $data variable then have to be stored as $links. Could it not just be run as foreach ($links) to begin with?

RiggsFolly · Accepted Answer · 2016-04-13T22:18:08.360

1

Assuming you do not write some code later that changes/destroys the $data variable between these 2 usages there is no problem doing this. It in fact reduces the runtime to reuse data if you can rather than getting it again.

What you need to remember is that ->fetchAll() returns all the rows from your resultset into an array. Its your array from then on, which you can do whatever you like with once the ->fetchAll() is completed

Second question:

the foreach

foreach ($data as $links)

processes over an array $data and returns one occurance (row in your case) at a time. So you have to give it another variable name. That name can be anything.

If you use sensible names for things they normally look like this

foreach ($rows as $row)

foreach ($sitelinks as $sitelink)

Using the plural for the original array and the singular for the single occurance returned by each iteration over the array.

So I would amend your code like so:

<?php
$sitelinks = $pdo->query("SELECT * 
                          FROM sitelinks 
                          WHERE `show` = 'yes' 
                          ORDER BY `Order` ASC")
                 ->fetchAll();

foreach ($sitelinks as $sitelink) {
    echo "\n<li class=\"linkitem\"><a href=\"{$sitelink['URL']}\">{$links['Text']}</a></li>";
}
?>

And the second use of the array

<?php
foreach ($sitelinks as $sitelink) {
    echo '\n<li class=\"desktoplinkitem\"><a href=\"{$sitelink['URL']}\">{$sitelink['Text']}</a></li>";
}
?>

edited Apr 13 '16 at 22:18

answered Apr 13 '16 at 16:39

RiggsFolly

93,638
21
103
149

Not my down vote but probably for not mentioning prepared statements. – david strachan Apr 13 '16 at 18:58
`fetchAll()` is slower, so I would personally just use `fetch(PDO::FETCH_ASSOC)`. – Tom Apr 13 '16 at 19:11
great that's all really helpful. Two further questions from this - @david strachan, you mention prepared statements - are they worth using if not using php variables within the query? Second question, so @RiggsFolly for my second block of code, would I need to use `foreach ($sitelinks as $sitelink)` again, or can I just echo `echo "\n
{$links['Text']}

hj8ag

Apr 13 '16 at 20:23

@davidstrachan Could be but as there are no parameters to bind, what would b the point other than adding an unnecessary round trip to the server to run a prepare and then to execute the query with no parameters – RiggsFolly Apr 13 '16 at 20:24

@hj8ag Yes, whatever you actually call the array, you would use the same variable name in both places, cause that what you called the variable. – RiggsFolly Apr 13 '16 at 20:25

@RiggsFolly I was wrong, it is the other way around, but `fetchall` does require more memory. More information about that can be found here: http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop – Tom Apr 13 '16 at 20:46

@RiggsFolly ok great, so just to be clear, the second code block can literally be (assuming the same variable is being used): `{$links['Text']}"; } ?>` – hj8ag Apr 13 '16 at 21:03

1

Yup. See amended answer – RiggsFolly Apr 13 '16 at 22:18

@RiggsFolly - Sorry, one more thing - is it possible to set an `ORDER` in the second block. I've noticed the first block is ordering the items in the way I want, but the second isn't... – hj8ag Apr 15 '16 at 08:57

1

You would have to re-sort that `$sitelinks` array [See the options available in the manual](http://php.net/manual/en/array.sorting.php) – RiggsFolly Apr 15 '16 at 09:09

ok perfect, thanks for linking this! I'm still a little confused, so I'm going to break out into a new question. Thanks for all your help though @Riggs – hj8ag Apr 15 '16 at 10:47

Reusing a PDO Query block in another area best practices

1 Answers1