PHP - MySQL to PDO

Question

So I decided to finally move over to PDO instead of using the old mysql_

But I noticed my site is loading slower. It's a table with 500 lines, and with my mysql_ queries it loaded slightly faster (0.5-1 second faster).

I wonder if it's just the way PDO works or if I've made some mistake somewhere. I did not change much from MySQL to PDO.

Here is my original mysql_ code:

<?php
                    $sql = mysql_query("SELECT * FROM rookstayers ORDER BY level DESC LIMIT 0, 500");
                    $id = 1;
                    $last_player_lvl = '';

                    while($row = mysql_fetch_array($sql)){
                        $name = $row['name'];
                        $level = $row['level'];
                        $world = $row['world'];
                        $account = $row['accountstatus'];
                        $status = $row['onlinestatus'];
                        $country = $row['country'];
                        $lastlogindate = $row['lastlogin'];
                        $lastlogin2 = utf8_decode($lastlogindate);
                        $lastlogin = str_replace("?", " ", $lastlogin2);
                        $onrow = '';
                        $typeServ = '';

                        $Date = $lastlogin;
                        $Date = substr($Date, 0, strpos($Date, " CE"));
                        $now  = date('Y-m-d');
                        $datetime1 = new DateTime($Date);
                        $datetime2 = new DateTime($now);
                        $interval = $datetime1->diff($datetime2);

                        $difference = $interval->format('%a days ago');

                        $player_name = urlencode($name);

                        if ($status == 1){
                            $status = 'Online';
                            $onrow = 'online';
                        } else {
                            $status = 'Offline';
                            $onrow = 'offline';
                        }

                        if ($account == 'Premium Account'){
                            $account = 'Premium';
                        } else {
                            $account = 'Free';
                        }

                        if ($world == 'Aurora' || $world == 'Aurera'){
                            $typeServ = 'activer';
                        } else {
                            $typeServ = '';
                        }

                    echo "<tr class=" . $typeServ . ">";
                        echo "<td align='right'>" . ( ($last_player_lvl == $row['level']) ? '' : $id ) . "</td>";
                        echo "<td align='center'><img src='../img/flags/" . $country . ".gif'></td>";
                        echo "<td><div class='". $onrow ."'></div></td>";
                        echo "<td><a href='../char/" . $player_name . "' class='playerlink'>" . $name . "</a></td>";
                        echo "<td>" . $level . "</td>";
                        echo "<td><a href='../world/" . $world ."' class='worldlink'>" . $world . "</a></td>";
                        echo "<td>"; if ($difference == 0){ echo "Today"; } elseif($difference == 1) { echo "Yesterday"; } else { echo $difference; } echo "</td>";
                        echo "<td>" . $account . "</td>";
                    echo "</tr>";

                    // Check if there are duplicate levels, if so, give them the same rank
                    if($last_player_lvl == $row['level']){
                        $id = $id;
                    }else{
                        $id++;
                    }

                    $last_player_lvl = $row['level'];
                }
                echo "</tbody>";
            echo "</table>";
            ?>

and here is my PDO code

<?php

                $sql = 'SELECT * FROM rookstayers ORDER BY level DESC LIMIT 0, 500';
                $id = 1;
                $last_player_lvl = '';

                foreach ($db->query($sql) as $row) {
                    $name = $row['name'];
                    $level = $row['level'];
                    $world = $row['world'];
                    $account = $row['accountstatus'];
                    $status = $row['onlinestatus'];
                    $country = $row['country'];
                    $lastlogindate = $row['lastlogin'];
                    $lastlogin2 = utf8_decode($lastlogindate);
                    $lastlogin = str_replace("?", " ", $lastlogin2);
                    $onrow = '';
                    $typeServ = '';

                    $Date = $lastlogin;
                    $Date = substr($Date, 0, strpos($Date, " CE"));
                    $now  = date('Y-m-d');
                    $datetime1 = new DateTime($Date);
                    $datetime2 = new DateTime($now);
                    $interval = $datetime1->diff($datetime2);

                    $difference = $interval->format('%a days ago');

                    $player_name = urlencode($name);

                    if ($status == 1){
                        $status = 'Online';
                        $onrow = 'online';
                    } else {
                        $status = 'Offline';
                        $onrow = 'offline';
                    }

                    if ($account == 'Premium Account'){
                        $account = 'Premium';
                    } else {
                        $account = 'Free';
                    }

                    if ($world == 'Aurora' || $world == 'Aurera'){
                        $typeServ = 'activer';
                    } else {
                        $typeServ = '';
                    }

                    echo "<tr class=" . $typeServ . ">";
                    echo "<td align='right'>" . ( ($last_player_lvl == $row['level']) ? '' : $id ) . "</td>";
                    echo "<td align='center'><img src='../img/flags/" . $country . ".gif'></td>";
                    echo "<td><div class='". $onrow ."'></div></td>";
                    echo "<td><a href='../char/" . $player_name . "' class='playerlink'>" . $name . "</a></td>";
                    echo "<td>" . $level . "</td>";
                    echo "<td><a href='../world/" . $world ."' class='worldlink'>" . $world . "</a></td>";
                    echo "<td>"; if ($difference == 0){ echo "Today"; } elseif($difference == 1) { echo "Yesterday"; } else { echo $difference; } echo "</td>";
                    echo "<td>" . $account . "</td>";
                    echo "</tr>";

                    // Check if there are duplicate levels, if so, give them the same rank
                    if($last_player_lvl == $row['level']){
                        $id = $id;
                    }else{
                        $id++;
                    }

                    $last_player_lvl = $row['level'];
                }
                echo "</tbody>";
                echo "</table>";
                ?>

maybe something to improve when it comes to the PDO part?

Answer 1

You are executing the query on every iteration of your foreach loop. See update.

Try replacing

foreach ($db->query($sql) as $row) { ...

with

$result = $db->query($sql);
foreach ($result as $row) {

---

Update: @mario is right. The foreach does't evaluate the expression on each iteration. I can't seem to find a conclusive answer as to why this would have solved the OPs issue; I still think there is something to it, but even in my own tests it seems that using the variable doesn't seem to have any significant effect on performance. If anyone has any more details to add, please do. :)