在另一个区域中重用PDO查询块的最佳实践

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    "
<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    "
<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?

Answer 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 "
<li class=\"linkitem\"><a href=\"{$sitelink['URL']}\">{$links['Text']}</a></li>";
}
?>

And the second use of the array

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