how do I count rows for a particular column in this query?

[kaiman]

Hi Everyone,

 

I am working on implementing a blog comment system using the query below to display comments. My question is what is the best way to do a row count for the id column with this query or do I need to do a second db query to accomplish this? The purpose of this is to echo out the number of comments for that post, before displaying them.

 

Any help is appreciated.

 

Thanks in advance,

 

kaiman

 

<?php
// get url variables
$post_id = mysql_real_escape_string($_GET['id']);

include ("../../scripts/includes/nl2p.inc.php");

// connects to server and selects database
include ("../../scripts/includes/dbconnect.inc.php");

// table name
$tbl_name3="blog_comments";

// select info from comments database
$result3 = mysql_query ("SELECT count(*) FROM $table_name3 WHERE id='$post_id' ORDER BY id DESC LIMIT 1") 
or trigger_error("A mysql error has occurred!");	
if (mysql_num_rows($result3) > 0 ) {
while($row = mysql_fetch_array($result3)) {
	extract($row);

	// display number of comments


	// display date
	$row_date = strtotime($row['date']);
	putenv("TZ=America/Denver");
	echo "<p class=\post\">On ".date('F, jS, Y', $row_date)." ";

	// display commenter name
	if($row['url'] == "") { 
		echo $row['name']." wrote:</p>\n"; 
	} 
    	else { 
		echo "<a href=\"".$row['url']."\" target=\"_blank\">".$row['name']."</a> wrote:</p>\n"; 
	} 

	// display content
	$comments = $row['comment'];
	echo nl2p($comments);
	}
}
else {
	echo "<p class=\"large_spacer\">No Comments</p>";
}		
?>

[Rifts]

Found this on the world wide web

 

// Query the database and get the count 
$penis= mysql_query("SELECT * FROM largeones"); 
$howmanypenis = mysql_num_rows($penis); 

// Display the results 
echo $howmanypenis ; 

[kaiman]

@ Rifts,

 

Thanks for the amusing reply  However, if I'm not mistaken, that will list all of the rows for the entire table (SELECT *), not just for one particular column. I am trying to just list the number of rows for 1 column (id), after selecting all of them (which I need to do to complete the rest of the query).

 

Any ideas how to do this?

 

Thanks again,

 

kaiman

[Pikachu2000]

If you aren't using the data set returned from the query, use a SELECT COUNT(`your_pk_index_field`) query. If you will be using the data, use your normal SELECT query in combination with mysql_num_rows().

[cyberRobot]

Using the mysql_num_rows() function should work if you do something like this.

 

...

// select info from comments database
$result3 = mysql_query ("SELECT count(*) FROM $table_name3 WHERE id='$post_id' ORDER BY id DESC LIMIT 1") or trigger_error("A mysql error has occurred!");
if (mysql_num_rows($result3) > 0 ) {
    // display number of comments
    echo 'Number of comments: ' . mysql_num_rows($result3);

    while($row = mysql_fetch_array($result3)) {
        extract($row);

        ...

 

 

Note that you'll probably need to remove the "LIMIT 1" part since you'll only get one result every time. You probably also don't need the count() function in the query.

[kaiman]

@cyberRobot

 

OK thanks I will give something like that a try.

 

kaiman