Problem with while in while statement

Johns3n · January 14, 2010

Hello PHPFreaks!

I am nearly done with my project which is a small Blog CMS, however I have run into a couple of problems that I have posted to this forum, in the hopes that you users might be able to assist me with!

I have written a SQL query that counts the amount of comments, that is "tied" to a Blog Post entry. The blog post entries are generated through a "while" a loop, since I found a way to paginate them if I ran it through a while statement. However, when I need to get my second SQL statement (the one that counted the amound of comments) into the while loop, I thought it was most prudent to just make another while loop inside it, however when I ran the script I could see that was not the case! Since it generated this:

(Blog post nr. 2 has 2 comments tied to it and Blog post nr. 1 has 1 comment tied to it in the Database by the way!)

BLOG POST NR. 2

BLAH BLAH BLAH (Blog post content)

1 Comments2 Comments

BLOG POST NR. 1

BLAH BLAH BLAH (Blog post content)

So this is what the page output for me! The Blog post entries are shown 100% correct, however the "Comment Counter" is the only thing that is off! And I think it might have something to do with while in while statement, but below I supplied the PHP code I wrote! and I hope that you can help me solve this little mystery.

<?php
		$num_query = "SELECT ". $db_prefix ."posts.id,". $db_prefix ."posts.title,". $db_prefix ."posts.content,". $db_prefix ."posts.date,". $db_prefix ."posts.author, COUNT(". $db_prefix ."comments.id) AS numComments FROM ". $db_prefix ."comments INNER JOIN ". $db_prefix ."posts ON ". $db_prefix ."comments.post_id = ". $db_prefix ."posts.id GROUP BY ". $db_prefix ."posts.id, ". $db_prefix ."posts.title,". $db_prefix ."posts.content,". $db_prefix ."posts.date,". $db_prefix ."posts.author";
		$num_result = mysql_query($num_query);

            // This is where the HTML output of our Blog Posts is generated

		while($row = mysql_fetch_array($result))
            {

            echo "<div class='post_entry'>";
            echo "<div class='post_title'><h2><a href='singlepost.php?itemid=" . $row['id'] . "' title='" . $row['title'] . "'>" . $row['title'] . "</a></h2></div>";
            echo "<div class='post_author'><p>Written by: " . $row['author'] . "</p></div>";
            echo "<div class='post_date'><p>Posted: " . $row['date'] . "</p></div>";
            echo "<div class='post_entry_content'><p>" . $row['content'] . "</p></div>";
            echo "<div class='post_comments'><a href='singlepost.php?itemid=" . $row['id'] . "#comments' title='Click here to view comments'>";

			while($num_row = mysql_fetch_assoc($num_result))
			{
			echo "". $num_row['numComments'] ." Comments</a>";
			}

            echo "</div>";

		}
        mysql_close($con);
        ?>

In advance thanks!

(P.S I am rookie at both PHP and SQL, so when if you need to explain something to me, do not take it I know things for granted xD)

Buddski · January 14, 2010

You best bet is to scrap the whole second loop and just add the COUNT(id) into the initial query and group it by `id`

Johns3n · January 14, 2010

You best bet is to scrap the whole second loop and just add the COUNT(id) into the initial query and group it by `id`

the initial query? Would you mean the "Comment Counter" query or the "Blog posts" query?

Seeing I already have one COUNT and GROUP BY in the "Comment Counter" query, it would make it hard adding another set of those, especially since the query it self works as intended when I user the statement ". $num_row['numComments'] ." defined by the AS in the "Comment Counter" query.

Buddski · January 14, 2010

By initial query I am referring to is the result stored in $result.

There is no need to create a second query to count the number of comments again..

Can you show us the first query.. the one that does the blog part.

Johns3n · January 14, 2010

Can you show us the first query.. the one that does the blog part.

Of course! However it is quite long! But it should be well commented so not so difficult to work out ^^

    <?php
        // Selects the Blog Posts table
        $tbl_name="". $db_prefix ."posts";		
        
        // Defines how many adjacent pages should be shown on each side on the pagination module
        $adjacents = 3;
        
        // Gets the total number of rows in data table. 
        $query = "SELECT COUNT(*) as num FROM $tbl_name";
        $total_pages = mysql_fetch_array(mysql_query($query));
        $total_pages = $total_pages[num];
        
        // The file which should include the Pagination Module (Always the name of the file this code appears in ex. Index.php)
        $targetpage = "index.php";
        
        // Defines the number of Blog Posts to show on each page
        $limit = 4;
        $page = $_GET['page'];
        if($page)
            // Defines the first item to display on this page
            $start = ($page - 1) * $limit;
        else
            // If no page variable is given, set start to 0
            $start = 0;
        
        // Get Blog Post data from MySQL database
        $sql = "SELECT * FROM $tbl_name ORDER BY id DESC LIMIT $start, $limit";
        $result = mysql_query($sql);
        
        // Setup variables for Pagination Module. (DO NOT EDIT THESE!)
        if ($page == 0) $page = 1;					// If no page var is given, default to 1.
        $prev = $page - 1;							// Previous page is page - 1
        $next = $page + 1;							// Next page is page + 1
        $lastpage = ceil($total_pages/$limit);		// Lastpage is = total pages / items per page, rounded up.
        $lpm1 = $lastpage - 1;						// Last page minus 1
        
        // Apply the variables and output the pagination Module. 
        // (We're actually saving the code to a variable in case we want to draw it more than once.)
        $pagination = "";
        if($lastpage > 1)
        {	
            $pagination .= "<div class=\"pagination\">";
            // Previous Button
            if ($page > 1) 
                $pagination.= "<a href=\"$targetpage?page=$prev\">« Previous</a>";
            else
                $pagination.= "<span class=\"disabled\">« Previous</span>";	
            
            // Pages
            if ($lastpage < 7 + ($adjacents * 2))
            {	
                for ($counter = 1; $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
                }
            }
            // Enough pages are detected to start hiding some in the Pagination Module
            elseif($lastpage > 5 + ($adjacents * 2))
            {
                // Close to beginning in Pagination Module: hide only later pages
                if($page < 1 + ($adjacents * 2))		
                {
                    for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<span class=\"current\">$counter</span>";
                        else
                            $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
                    }
                    $pagination.= "...";
                    $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                    $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
                }
                // In the middle of the Pagination Module: Hide some pages front and some back
                elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
                {
                    $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                    $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                    $pagination.= "...";
                    for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<span class=\"current\">$counter</span>";
                        else
                            $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
                    }
                    $pagination.= "...";
                    $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                    $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
                }
                // Close to end of the Pagination Module: Hide only earliere pages
                else
                {
                    $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                    $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                    $pagination.= "...";
                    for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<span class=\"current\">$counter</span>";
                        else
                            $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
                    }
                }
            }
            
            // Next Button
            if ($page < $counter - 1) 
                $pagination.= "<a href=\"$targetpage?page=$next\">Next »</a>";
            else
                $pagination.= "<span class=\"disabled\">Next »</span>";
            $pagination.= "</div>\n";		
        }
    ?>
    <?php
		$num_query = "SELECT ". $db_prefix ."posts.id,". $db_prefix ."posts.title,". $db_prefix ."posts.content,". $db_prefix ."posts.date,". $db_prefix ."posts.author, COUNT(". $db_prefix ."comments.id) AS numComments FROM ". $db_prefix ."comments INNER JOIN ". $db_prefix ."posts ON ". $db_prefix ."comments.post_id = ". $db_prefix ."posts.id GROUP BY ". $db_prefix ."posts.id, ". $db_prefix ."posts.title,". $db_prefix ."posts.content,". $db_prefix ."posts.date,". $db_prefix ."posts.author";
		$num_result = mysql_query($num_query);

            // This is where the HTML output of our Blog Posts is generated

		while($row = mysql_fetch_array($result))
            {

            echo "<div class='post_entry'>";
            echo "<div class='post_title'><h2><a href='singlepost.php?itemid=" . $row['id'] . "' title='" . $row['title'] . "'>" . $row['title'] . "</a></h2></div>";
            echo "<div class='post_author'><p>Written by: " . $row['author'] . "</p></div>";
            echo "<div class='post_date'><p>Posted: " . $row['date'] . "</p></div>";
            echo "<div class='post_entry_content'><p>" . $row['content'] . "</p></div>";
		echo "<div class='post_comments'><a href='singlepost.php?itemid=" . $row['id'] . "#comments' title='Click here to view comments'>";

			while($num_row = mysql_fetch_assoc($num_result))
			{
			echo "". $num_row['numComments'] ." Comments</a>";
			}

            echo "</div>";

		}
        mysql_close($con);
    ?>

P.S There is a config.php file declared in the header of the file, so if you wondering about a connection string! I can assure you it's there ^^

Buddski · January 14, 2010

You could just do this..

$sql = "
SELECT *, COUNT(post_id) as `commentCount`
FROM $tbl_name 
Left Join ".$db_prefix."comments ON id = post_id
ORDER BY id DESC 
LIMIT $start, $limit";

$result = mysql_query($sql);

instead of

// Get Blog Post data from MySQL database
        $sql = "SELECT * FROM $tbl_name ORDER BY id DESC LIMIT $start, $limit";
        $result = mysql_query($sql);

Johns3n · January 14, 2010

You could just do this..

$sql = "
SELECT *, COUNT(post_id) as `commentCount`
FROM $tbl_name 
Left Join ".$db_prefix."comments ON id = post_id
ORDER BY id DESC 
LIMIT $start, $limit";

$result = mysql_query($sql);

instead of

// Get Blog Post data from MySQL database
        $sql = "SELECT * FROM $tbl_name ORDER BY id DESC LIMIT $start, $limit";
        $result = mysql_query($sql);

Tried what you suggest and it gave me "a error" I have seen before where it renders the page, but every blog post is missing.

Supplied the updated code:

<?php
        // Selects the Blog Posts table
        $tbl_name="". $db_prefix ."posts";		
        
        // Defines how many adjacent pages should be shown on each side on the pagination module
        $adjacents = 3;
        
        // Gets the total number of rows in data table. 
        $query = "SELECT COUNT(*) as num FROM $tbl_name";
        $total_pages = mysql_fetch_array(mysql_query($query));
        $total_pages = $total_pages[num];
        
        // The file which should include the Pagination Module (Always the name of the file this code appears in ex. Index.php)
        $targetpage = "index.php";
        
        // Defines the number of Blog Posts to show on each page
        $limit = 4;
        $page = $_GET['page'];
        if($page)
            // Defines the first item to display on this page
            $start = ($page - 1) * $limit;
        else
            // If no page variable is given, set start to 0
            $start = 0;
        
        // Get Blog Post data from MySQL database

	$sql = "
	SELECT *, COUNT(post_id) as `commentCount`
	FROM $tbl_name
	Left Join ".$db_prefix."comments ON id = post_id
	ORDER BY id DESC
	LIMIT $start, $limit";

	$result = mysql_query($sql);

        //$sql = "SELECT * FROM $tbl_name ORDER BY id DESC LIMIT $start, $limit";
        //$result = mysql_query($sql);
        
        // Setup variables for Pagination Module. (DO NOT EDIT THESE!)
        if ($page == 0) $page = 1;					// If no page var is given, default to 1.
        $prev = $page - 1;							// Previous page is page - 1
        $next = $page + 1;							// Next page is page + 1
        $lastpage = ceil($total_pages/$limit);		// Lastpage is = total pages / items per page, rounded up.
        $lpm1 = $lastpage - 1;						// Last page minus 1
        
        // Apply the variables and output the pagination Module. 
        // (We're actually saving the code to a variable in case we want to draw it more than once.)
        $pagination = "";
        if($lastpage > 1)
        {	
            $pagination .= "<div class=\"pagination\">";
            // Previous Button
            if ($page > 1) 
                $pagination.= "<a href=\"$targetpage?page=$prev\">« Previous</a>";
            else
                $pagination.= "<span class=\"disabled\">« Previous</span>";	
            
            // Pages
            if ($lastpage < 7 + ($adjacents * 2))
            {	
                for ($counter = 1; $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
                }
            }
            // Enough pages are detected to start hiding some in the Pagination Module
            elseif($lastpage > 5 + ($adjacents * 2))
            {
                // Close to beginning in Pagination Module: hide only later pages
                if($page < 1 + ($adjacents * 2))		
                {
                    for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<span class=\"current\">$counter</span>";
                        else
                            $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
                    }
                    $pagination.= "...";
                    $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                    $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
                }
                // In the middle of the Pagination Module: Hide some pages front and some back
                elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
                {
                    $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                    $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                    $pagination.= "...";
                    for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<span class=\"current\">$counter</span>";
                        else
                            $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
                    }
                    $pagination.= "...";
                    $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                    $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
                }
                // Close to end of the Pagination Module: Hide only earliere pages
                else
                {
                    $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                    $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                    $pagination.= "...";
                    for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<span class=\"current\">$counter</span>";
                        else
                            $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
                    }
                }
            }
            
            // Next Button
            if ($page < $counter - 1) 
                $pagination.= "<a href=\"$targetpage?page=$next\">Next »</a>";
            else
                $pagination.= "<span class=\"disabled\">Next »</span>";
            $pagination.= "</div>\n";		
        }
    ?>
    <?php
		/*$num_query = "SELECT ". $db_prefix ."posts.id,". $db_prefix ."posts.title,". $db_prefix ."posts.content,". $db_prefix ."posts.date,". $db_prefix ."posts.author, COUNT(". $db_prefix ."comments.id) AS numComments FROM ". $db_prefix ."comments INNER JOIN ". $db_prefix ."posts ON ". $db_prefix ."comments.post_id = ". $db_prefix ."posts.id GROUP BY ". $db_prefix ."posts.id, ". $db_prefix ."posts.title,". $db_prefix ."posts.content,". $db_prefix ."posts.date,". $db_prefix ."posts.author";
		$num_result = mysql_query($num_query);
		while($num_row = mysql_fetch_assoc($num_result))*/
            // This is where the HTML output of our Blog Posts is generated

		while($row = mysql_fetch_array($result))
            {

            echo "<div class='post_entry'>";
            echo "<div class='post_title'><h2><a href='singlepost.php?itemid=" . $row['id'] . "' title='" . $row['title'] . "'>" . $row['title'] . "</a></h2></div>";
            echo "<div class='post_author'><p>Written by: " . $row['author'] . "</p></div>";
            echo "<div class='post_date'><p>Posted: " . $row['date'] . "</p></div>";
            echo "<div class='post_entry_content'><p>" . $row['content'] . "</p></div>";
		echo "<div class='post_comments'><a href='singlepost.php?itemid=" . $row['id'] . "#comments' title='Click here to view comments'>". $row['commentCount'] ." Comments</a>";		
            echo "</div>";

		}
        mysql_close($con);
    ?>

The "while" in "while" statement I made first, is the closest I have been to success so far

Buddski · January 14, 2010

Oops

$sql = "
      SELECT *, COUNT(post_id) as `commentCount`
      FROM $tbl_name
      Left Join ".$db_prefix."comments ON id = post_id
GROUP BY id
      ORDER BY id DESC
      LIMIT $start, $limit";

Should fix it..

Johns3n · January 14, 2010

Oops

$sql = "
      SELECT *, COUNT(post_id) as `commentCount`
      FROM $tbl_name
      Left Join ".$db_prefix."comments ON id = post_id
GROUP BY id
      ORDER BY id DESC
      LIMIT $start, $limit";

Should fix it..

Unfortunately it didn't, same problem still

however I read over your new SQL query and you wrote "COUNT(post_id) as `commentCount`FROM $tbl_name" However $tblname is my ".$db_prefix."posts table, which doesn't contain a value called "post_id" I supplied my DB structure below if it's any help

".$db_prefix."posts

id INT (Primary)

title (varchar 250)

content (text)

date (varchar 250)

author (varchar 250)

".$db_prefix."comments

id INT (Primary)

post_id INT

comment_name (varchar 250)

comment_content (text)

comment_date (varchar 250)

Now the Post_id in the comments table are matching to Id of the post, that the comment is "tied" with

Buddski · January 14, 2010

Ok I can see a mysql error now without seeing it

Firstly, $tblname doesnt need a post_id because we are joining onto the comments table which HAS a post_id

Anyway on with the code..

$sql = "
      SELECT *, COUNT(post_id) as `commentCount`
      FROM ".$tbl_name."
      Left Join ".$db_prefix."comments ON ".$tbl_name.".id = post_id
GROUP BY ".$tbl_name.".id
      ORDER BY ".$tbl_name.".id DESC
      LIMIT $start, $limit";

It would have been conflicting with the id field names from both table so we have to define which table we are talking about when referencing id now..

Let me know how this goes for you..

Debugging things like this is easier with mysql_error() FYI

Johns3n · January 14, 2010

Ok I can see a mysql error now without seeing it

Firstly, $tblname doesnt need a post_id because we are joining onto the comments table which HAS a post_id

Anyway on with the code..
$sql = "
      SELECT *, COUNT(post_id) as `commentCount`
      FROM ".$tbl_name."
      Left Join ".$db_prefix."comments ON ".$tbl_name.".id = post_id
GROUP BY ".$tbl_name.".id
      ORDER BY ".$tbl_name.".id DESC
      LIMIT $start, $limit";
It would have been conflicting with the id field names from both table so we have to define which table we are talking about when referencing id now..

Let me know how this goes for you..

Debugging things like this is easier with mysql_error() FYI

Hehe would do you know it worked The counting is correct now! BUT!

Another problem just occurred now sadly

The id value for the links (" . $row['id'] . ") is no longer accurate and not sending you to the correct singlepost.php anymore

            echo "<div class='post_title'><h2><a href='singlepost.php?itemid=" . $row['id'] . "' title='" . $row['title'] . "'>" . $row['title'] . "</a></h2></div>";
            echo "<div class='post_author'><p>Written by: " . $row['author'] . "</p></div>";
            echo "<div class='post_date'><p>Posted: " . $row['date'] . "</p></div>";
            echo "<div class='post_entry_content'><p>" . $row['content'] . "</p></div>";
            echo "<div class='post_comments'><a href='singlepost.php?itemid=" . $row['id'] . "#comments' title='Click here to view comments'>". $row['commentCount'] ." Comments</a>";

*Sigh* It's always something ^^

But I really appreciate you helping me out here mate

Buddski · January 14, 2010

Alrighty and No worries..

$sql = "
      SELECT ".$tbl_name.".*, COUNT(post_id) as `commentCount`
      FROM ".$tbl_name."
      Left Join ".$db_prefix."comments ON ".$tbl_name.".id = post_id
GROUP BY ".$tbl_name.".id
      ORDER BY ".$tbl_name.".id DESC
      LIMIT $start, $limit";

Should cover it.

Johns3n · January 14, 2010

Alrighty and No worries..

$sql = "
      SELECT ".$tbl_name.".*, COUNT(post_id) as `commentCount`
      FROM ".$tbl_name."
      Left Join ".$db_prefix."comments ON ".$tbl_name.".id = post_id
GROUP BY ".$tbl_name.".id
      ORDER BY ".$tbl_name.".id DESC
      LIMIT $start, $limit";

Should cover it.

Buddski you sir! are a god!

Thanks alot for the help mate! Really appreciate it!

(maybe I should check if the pagination still works ^^)

Buddski · January 14, 2010

Im not a god.. Just a dude with alot of free time..

Johns3n · January 14, 2010

Im not a god.. Just a dude with alot of free time..

Pagination works still!

Hehe well this dude here is thanking you for having that free time help me out here! been sweating with this for a long time now! Thanks alot mate!

Buddski · January 14, 2010

Anytime, glad to help..

Sign In

Problem with while in while statement

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information