Johns3n Posted January 14, 2010 Share Posted 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) Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/ Share on other sites More sharing options...
Buddski Posted January 14, 2010 Share Posted 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` Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994844 Share on other sites More sharing options...
Johns3n Posted January 14, 2010 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994848 Share on other sites More sharing options...
Buddski Posted January 14, 2010 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994852 Share on other sites More sharing options...
Johns3n Posted January 14, 2010 Author Share Posted 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 ^^ Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994858 Share on other sites More sharing options...
Buddski Posted January 14, 2010 Share Posted 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); Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994861 Share on other sites More sharing options...
Johns3n Posted January 14, 2010 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994868 Share on other sites More sharing options...
Buddski Posted January 14, 2010 Share Posted 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.. Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994870 Share on other sites More sharing options...
Johns3n Posted January 14, 2010 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994875 Share on other sites More sharing options...
Buddski Posted January 14, 2010 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994879 Share on other sites More sharing options...
Johns3n Posted January 14, 2010 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994884 Share on other sites More sharing options...
Buddski Posted January 14, 2010 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994887 Share on other sites More sharing options...
Johns3n Posted January 14, 2010 Author Share Posted 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 ^^) Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994894 Share on other sites More sharing options...
Buddski Posted January 14, 2010 Share Posted January 14, 2010 Im not a god.. Just a dude with alot of free time.. Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994895 Share on other sites More sharing options...
Johns3n Posted January 14, 2010 Author Share Posted 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! Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994897 Share on other sites More sharing options...
Buddski Posted January 14, 2010 Share Posted January 14, 2010 Anytime, glad to help.. Quote Link to comment https://forums.phpfreaks.com/topic/188446-problem-with-while-in-while-statement/#findComment-994901 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.