Spot The Bug: Whiling Away Your Data

They're bad, they're mind-boggling, they're bugs! And we're here today to catch another one of those sneaky little buggers in the latest installment of Spot The Bug.

I know you're
out there, bugs.

So lately, I've been fooling around with WordPress. Don't ask me why; that's neither here nor there for today. Also, this particular bug had nothing to do with WordPress and everything to do with PHP. You'll see why in a bit.

So I had set up a dummy WordPress site in a Mac environment with a whole bunch of Lorem Ipsum. I was poking around WordPress's database schema using MySQL and wrote a little bit of PHP code in my local environment to list out the titles of the wp_posts table.

<?php
    $conn = new mysqli("127.0.0.1", "root", "", "wptest");

    if ($conn->connect_error)
    {
        die("DB connection failed.");
    }

    $sql = "SELECT * FROM wp_posts WHERE post_type='post' AND post_status='publish' ORDER BY post_date DESC";
    $result = $conn->query($sql);

    if (mysqli_num_rows($result) === 0) die("No posts!");
    
    while($row = $result->fetch_assoc())
    {    
        echo "<h1>" . $row["post_title"] . "</h1>";            
    }            

    $conn->close();
?>

So it was going swimmingly so far.

And then next I decided to list out the comments on each post, from the wp_comments table. A little nested While loop should do the trick.

<?php
    $conn = new mysqli("127.0.0.1", "root", "", "wptest");

    if ($conn->connect_error)
    {
        die("DB connection failed.");
    }

    $sql = "SELECT * FROM wp_posts WHERE post_type='post' AND post_status='publish' ORDER BY post_date DESC";
    $result = $conn->query($sql);

    if (mysqli_num_rows($result) === 0) die("No posts!");
    
    while($row = $result->fetch_assoc())
    {    
        echo "<h1>" . $row["post_title"] . "</h1>";

        $sql = "SELECT * FROM wp_comments WHERE comment_post_id = " . $row["ID"];

        $result = $conn->query($sql);

        if (mysqli_num_rows($result) === 0) break;

        while($row_comments = $result->fetch_assoc())
        {
            echo "<p>" . $row_comments["comment_content"] . "</p>";
        }            
    }            

    $conn->close();
?>

And this happened! The first post title and its comments were laid out. No errors were seen, but the rest of the content disappeared!

What went wrong

As it turned out, it was me who was being an idiot. I'd appended "_comments" to the name of the associative array, row, to avoid confusing the program, right? Well, I forgot to do the same for result.

$result = $conn->query($sql);

if (mysqli_num_rows($result) === 0) break;

while($row_comments = $result->fetch_assoc())
{
    echo "<p>" . $row_comments["comment_content"] . "</p>";
}

Why it went wrong

So if result, within the very first iteration of the outer While loop, became the object that contained the dataset of the comments of the first post, then quite naturally there would be no next post to fetch!

while($row = $result->fetch_assoc())
{    
    echo "<h1>" . $row["post_title"] . "</h1>";

    $sql = "SELECT * FROM wp_comments WHERE comment_post_id = " . $row["ID"];

    $result = $conn->query($sql);

    if (mysqli_num_rows($result) === 0) break;

    while($row_comments = $result->fetch_assoc())
    {
        echo "<p>" . $row_comments["comment_content"] . "</p>";
    }            
}

How I fixed it

Quite easily done! Just add "_comments" to the name of result!

<?php
    $conn = new mysqli("127.0.0.1", "root", "", "wptest");

    if ($conn->connect_error)
    {
        die("DB connection failed.");
    }

    $sql = "SELECT * FROM wp_posts WHERE post_type='post' AND post_status='publish' ORDER BY post_date DESC";
    $result = $conn->query($sql);

    if (mysqli_num_rows($result) === 0) die("No posts!");
    
    while($row = $result->fetch_assoc())
    {    
        echo "<h1>" . $row["post_title"] . "</h1>";

        $sql = "SELECT * FROM wp_comments WHERE comment_post_id = " . $row["ID"];

        $result_comments = $conn->query($sql);

        if (mysqli_num_rows($result_comments) === 0) break;

        while($row_comments = $result_comments->fetch_assoc())
        {
            echo "<p>" . $row_comments["comment_content"] . "</p>";
        }            
    }            

    $conn->close();
?>

And there you go. That beautiful, beautiful Lorem Ipsum.

Moral of the story

In nested loops of any kind, repeating the declaration of a variable that was used in the outer loop is a very bad idea. Even if nothing untoward occurs now, it's an accident waiting to happen.

Thanks for reading. _comments welcome!
T___T