Johns3n Posted January 12, 2010 Share Posted January 12, 2010 Hello PHPfreaks I am having some trouble with the mysql_num_rows, i was written a small script that displays the total number of comments that was connected with a blog post, i got it to succesfully display a number, but unfortunally it took the total number of comments, instead of the numbers actually matching with that blog post. Please bear in mind, i am a complete novice at PHP. So please execuse me, if my question is complete idiotic one! here is the code that i use: <?php $amount = mysql_query("SELECT ". $db_prefix ."posts.id, ". $db_prefix ."comments.post_id "."FROM ". $db_prefix ."posts, ". $db_prefix ."comments "."WHERE ". $db_prefix ."posts.id = ". $db_prefix ."comments.post_id", $con); $num_rows = mysql_num_rows($amount); // 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'>" . $num_rows . " Comments</a>"; echo "</div>"; } mysql_close($con); ?> So in advance, thanks for your help! Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/ Share on other sites More sharing options...
lonewolf217 Posted January 12, 2010 Share Posted January 12, 2010 without knowing your DB structure, the best advice that I can give would be to say prior to running the SQL query to get the number of comments, echo out the SQL string that is used. Throw that through SQL manually to see what is wrong. are you getting the wrong ID#, is your query formatted incorrectly? These things are easiest to troubleshoot when you see the exact string being processed rather than a string filled with unknown variables Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993788 Share on other sites More sharing options...
mikesta707 Posted January 12, 2010 Share Posted January 12, 2010 Yeah, num_rows will return all the comments (since your query effectively retrieves all the comments from the table. You probably want to use Mysql's count() function instead of mysql num rows. As lonewolf said, I don't know your DB structure, so it will be somewhat difficult to guess what the changed query would be, but something like $sql = "SELECT posts.id, comments.post_id, count(comments.post_id) as numComments ..... "; you may also want to use group by in your query Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993791 Share on other sites More sharing options...
Johns3n Posted January 12, 2010 Author Share Posted January 12, 2010 thanks both of you! Would be any assistance if i posted, my DB structure? Because i would really like some help with this since i think i got over my head here Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993793 Share on other sites More sharing options...
PFMaBiSmAd Posted January 12, 2010 Share Posted January 12, 2010 Your code is using two different result sets $amount and $result. I'll guess one is all the posts and one is just those matching a specific thread. Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993797 Share on other sites More sharing options...
Johns3n Posted January 12, 2010 Author Share Posted January 12, 2010 Your code is using two different result sets $amount and $result. I'll guess one is all the posts and one is just those matching a specific thread. Indeed! $result is all of my posts, and works without a problem i just need a small "by" script that counted the comments for each blog post If it's any help my db structure: comments{ id post_id comment_name comment_content comment_date } posts{ id title content date author } Hope this helps? Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993804 Share on other sites More sharing options...
Johns3n Posted January 12, 2010 Author Share Posted January 12, 2010 I worked abit around with code that mikesta707 provided, I got the page rendered, unfortunaly it still doesn't show the correct number of comment posts, again! im very sorry! I am abit of a idiot when it comes to PHP, so any help is appreciated! <?php $amount = mysql_query("SELECT ". $db_prefix ."posts.id, ". $db_prefix ."comments.post_id, count(". $db_prefix ."comments.post_id) as numComments", $con); $num_rows = mysql_num_rows($amount); // 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'>" . $num_rows . " Comments</a>"; echo "</div>"; } mysql_close($con); ?> Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993841 Share on other sites More sharing options...
mikesta707 Posted January 12, 2010 Share Posted January 12, 2010 instead of using $num_rows you would probably want to use $row['numComments'] Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993845 Share on other sites More sharing options...
Johns3n Posted January 12, 2010 Author Share Posted January 12, 2010 Tried that and didn't work But aren't we forgetting a kind of "WHERE post.id=comments.post_id" somewhere? I could be wrong? I have posted my DB structure as you mentioned about 2 posts up code so far: <?php $amount = mysql_query("SELECT ". $db_prefix ."posts.id, ". $db_prefix ."comments.post_id, count(". $db_prefix ."comments.post_id) as numComments", $con); $num_rows = mysql_num_rows($amount); // 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['numComments'] . " Comments</a>"; echo "</div>"; } mysql_close($con); ?> Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993850 Share on other sites More sharing options...
mikesta707 Posted January 12, 2010 Share Posted January 12, 2010 oh, didn't notice that. yeah you should definitely include your WHERE clause you had before Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993859 Share on other sites More sharing options...
Johns3n Posted January 12, 2010 Author Share Posted January 12, 2010 $amount = mysql_query("SELECT ". $db_prefix ."posts.id, ". $db_prefix ."comments.post_id, WHERE ". $db_prefix ."posts.id = ". $db_prefix ."comments.post_id" count(". $db_prefix ."comments.post_id) as numComments", $con); Would this be an appropriate position for the where clause? Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993863 Share on other sites More sharing options...
mikesta707 Posted January 12, 2010 Share Posted January 12, 2010 no, the Where clause has to go after the select clause. so it should be select blah blah blah where blah blah the count() part is part of the select clause Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993872 Share on other sites More sharing options...
Johns3n Posted January 12, 2010 Author Share Posted January 12, 2010 soo this would be correct? However it still doesn't work Sorry to be this incredible pain! <?php $amount = mysql_query("SELECT ". $db_prefix ."posts.id, ". $db_prefix ."comments.post_id, count(". $db_prefix ."comments.post_id) as numComments WHERE ". $db_prefix ."posts.id = ". $db_prefix ."comments.post_id", $con); $num_rows = mysql_num_rows($amount); // 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['numComments'] . " Comments</a>"; echo "</div>"; } mysql_close($con); ?> Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993876 Share on other sites More sharing options...
mikesta707 Posted January 12, 2010 Share Posted January 12, 2010 what exactly does $row['numComments'] print out? is it the same as mysql_num_rows()? Try using group by (I gave a link above) and grouping by the posts.id Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993891 Share on other sites More sharing options...
Johns3n Posted January 12, 2010 Author Share Posted January 12, 2010 what exactly does $row['numComments'] print out? is it the same as mysql_num_rows()? Try using group by (I gave a link above) and grouping by the posts.id Okay thanks for the advice i tweaked it abit around and finally got the MySQL query to output the right numbers in PHPmyadmin, however now the only problem is i can't make it show... the mysql query is 100% correct now.. just a matter of showing the data now code atm: <?php $amount = mysql_query("SELECT ". $prefix ."posts.id,". $prefix ."posts.title,". $prefix ."posts.content,". $prefix ."posts.date,". $prefix ."posts.author, COUNT(". $prefix ."comments.id) AS numComments FROM ". $prefix ."comments INNER JOIN ". $prefix ."posts ON ". $prefix ."comments.post_id = ". $prefix ."posts.id GROUP BY ". $prefix ."posts.id, ". $prefix ."posts.title,". $prefix ."posts.content,". $prefix ."posts.date,". $prefix ."posts.author", $con); // 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'>" . $amount['numComments'] . " Comments</a>"; echo "</div>"; } mysql_close($con); ?> Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993911 Share on other sites More sharing options...
Johns3n Posted January 12, 2010 Author Share Posted January 12, 2010 Okay, since im 100% that the mysql is correct, il post the entire file that i am working in right now so that you may get a better understanding of how it works.. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta name="Description" content="INSERT DISCRIPTION OF YOUR WEBSITE HERE, THIS DISCRIPTION IS ALSO USED FOR YOUR WEBSITE WHEN LISTED IN GOOGLE SEARCH RESULTS" /> <meta name="Keywords" content="INSERT YOUR WEBSITE KEYWORDS HERE SEPERATED BY COMMAS" /> <meta name="Robots" content="ALL" /> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="alternate" title="RSS" href="http://www.prankstar.dk/johns3n/lork/rss.php" type="application/rss+xml" /> <link href="default.css" rel="stylesheet" type="text/css" media="all" /> <title>TITLE OF YOUR WEBSITE</title> </head> <body> <div class="wrapper"> <div class="posts"> <img src="gfx/logo.jpg" alt="Lörk Logo" /> <!-- Insert Posts Start --> <?php include("config.php"); ?> <?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 $amount = "SELECT ". $prefix ."posts.id,". $prefix ."posts.title,". $prefix ."posts.content,". $prefix ."posts.date,". $prefix ."posts.author, COUNT(". $prefix ."comments.id) AS numComments FROM ". $prefix ."comments INNER JOIN ". $prefix ."posts ON ". $prefix ."comments.post_id = ". $prefix ."posts.id GROUP BY ". $prefix ."posts.id, ". $prefix ."posts.title,". $prefix ."posts.content,". $prefix ."posts.date,". $prefix ."posts.author"; $num = mysql_query($amount,$con); // 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'>" . $num['numComments'] . " Comments</a>"; echo "</div>"; } mysql_close($con); ?> <!-- Insert Pagination Module --> <?=$pagination?> <!-- Call Posts End --> </div> </div> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/188245-problem-with-mysql_num_rows/#findComment-993916 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.