Problem with mysql_num_rows

Johns3n · 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!

lonewolf217 · 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

mikesta707 · 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

Johns3n · 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

PFMaBiSmAd · 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.

Johns3n · 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?

Johns3n · 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);
        ?>

mikesta707 · January 12, 2010

instead of using $num_rows you would probably want to use $row['numComments']

Johns3n · 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);
        ?>

mikesta707 · January 12, 2010

oh, didn't notice that. yeah you should definitely include your WHERE clause you had before

Johns3n · 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?

mikesta707 · 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

Johns3n · 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);
        ?>

mikesta707 · 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

Johns3n · 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);
        ?>

Johns3n · 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>

Sign In

Problem with mysql_num_rows

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