Page 1 of 3

counting replies

Posted: Wed Mar 02, 2005 4:39 am
by pleigh
im trying to count the replies of a certain message/thread

Code: Select all

$query = "SELECT comment FROM comments, posts, users 
	WHERE comments.postID = posts.postID AND comments.firstname = users.firstname";
	$result = @mysql_query($query);
	$num = mysql_num_rows($result);
	
	if ($num >= 0)
	{
		echo "$num";
	}
the problem is that it counts all the replies in the table and not the number of replies of a certain message

Posted: Wed Mar 02, 2005 4:45 am
by CoderGoblin
You need to restrict the details with a "PostID" or somesuch as shown below. Without it you will get anything that links together.

Code: Select all

$query = "SELECT comment FROM comments, posts, users
   WHERE comments.postID = posts.postID AND comments.firstname = users.firstname AND postID=$postID";

Posted: Wed Mar 02, 2005 4:49 am
by pleigh
no luck, i tried to put the additional conditions but i still receive errors...

Posted: Wed Mar 02, 2005 4:58 am
by n00b Saibot
you recieved error because Coder's code (now ain't that funny :lol:) misses one thing. the additional conditional postID does not have a qualified table name in front of it. hence the error.
change it to

Code: Select all

$query = "SELECT comment FROM comments, posts, users 
   WHERE comments.postID = posts.postID AND comments.firstname = users.firstname AND comments.postID=$postID";

Posted: Wed Mar 02, 2005 4:59 am
by CoderGoblin
What "errors" ?

With the information supplied it is difficult to be of assistance. Remember we do not understand your database structure, we can only guess. We do not know what php variables you have around so again we can only guess but make it consise and (supply up with too much non-needed information and we are likely to ignore it :wink: ).

You say you are trying "to count the replies of a certain message/thread" at present you are joining it to the users table. Is this necessary ?

What I suggest is to ignore PHP at present and go into your database. Try selecting things and experimenting with SQL until you can understand and get what you need. Then using this knowledge you will understand what parameters you need to pass into the SQL from PHP.

Posted: Wed Mar 02, 2005 5:08 am
by pleigh
ok here's my code

Code: Select all

$display = 10;
	
	
	if (isset($_GETї'np'])) 
	{ 
		$num_pages = $_GETї'np'];
	} 
	else 
	{ 
		$query = "SELECT * FROM posts"; 
		$query_result = mysql_query ($query);
		$num_records = @mysql_num_rows ($query_result);
		
		if ($num_records > $display) 
		{ 
			$num_pages = ceil ($num_records/$display);
		} 
		else 
		{
			$num_pages = 1;
		}
	}
	
	
	if (isset($_GETї's'])) 
	{ 
		$start = $_GETї's'];
	} 
	else 
	{
		$start = 0;
	}
			
	// Make the query.
	$query = "SELECT DATE_FORMAT(postupdate, '%M %D, %Y - %l:%i %p'), title, postID, firstname FROM posts AS p, users AS u 
			WHERE p.userID = u.userID ORDER BY postupdate DESC LIMIT $start, $display";		
	$result = @mysql_query ($query); 
	$num = mysql_num_rows ($result); 
	
	if ($num > 0) 
	{ 	
		if ($num_pages > 1) 
		{			
			
			$current_page = ($start/$display) + 1;
			
			
			if ($current_page != 1) 
			{
				echo '<a href="report.php?s=' . ($start - $display) . 
				'&np=' . $num_pages . '" class=under>Previous</a> ';
			&#125;
			
			
			for ($i = 1; $i <= $num_pages; $i++) 
			&#123;
				if ($i != $current_page) 
				&#123;
					echo '<a href="report.php?s=' . (($display * ($i - 1))) . 
					'&np=' . $num_pages . '" class=under>' . $i . '</a> ';
				&#125; 
				else 
				&#123;
					echo $i . ' ';
				&#125;
			&#125;
			
			
			if ($current_page != $num_pages) 
			&#123;
				echo '<a href="report.php?s=' . ($start + $display) . 
				'&np=' . $num_pages . '" class=under>Next</a>';
			&#125;
									
		&#125; 
		
		
		echo "<tr align=center class=tablehead><td width=20% class=tableborder><table><tr><td><b>NAME</b></td></tr></table></td>
			<td width=50% class=tableborder><table><tr><td><b>TOPIC</b></td></tr></table></td>
			<td width=10% class=tableborder><table><tr><td><b>REPLIES</b></td></tr></table></td>
			<td width=20% class=tableborder><table><tr><td><b>DATE</b></td></tr></table></td></tr>";
		
		
		while ($row = mysql_fetch_array($result, MYSQL_NUM)) 
		&#123;			
			$row&#1111;0] = '<font size=1>'.$row&#1111;0].'</font>';
  			echo "<tr><td width=20% class=tableborder align=center><table><tr><td><b><a href="" class="under">".strtolower($row&#1111;3])."</a><b></td></tr></table></td>
			<td width=50% class=tableborder><table><tr><td><a href="reportview.php?pid=&#123;$row&#1111;2]&#125;" class="under" style="font-weight: bold;">$row&#1111;1]</a></td></tr></table></td>
			<td width=10% class=tableborder><table><tr><td>".replies()."</td></tr></table></td>
			<td width=20% class=tableborder><table><tr><td>$row&#1111;0]</td></tr></table></td></tr>";  
			
		&#125;		
	
		mysql_free_result ($result); 	
	
	&#125; 
	else 
	&#123; 
		echo '<h3>There are no records to show.</h3>'; 
	&#125;
	
	mysql_close();
then im trying to insert a function

Code: Select all

$query = "SELECT comment FROM comments, posts, users
   WHERE comments.postID = posts.postID AND comments.firstname = users.firstname AND comments.postID=$postID";
	$result = @mysql_query($query);
	$num = mysql_num_rows($result);
	
	if ($num >= 0)
	&#123;
		echo "$num";
	&#125;
here

Code: Select all

while ($row = mysql_fetch_array($result, MYSQL_NUM)) 
		&#123;			
			$row&#1111;0] = '<font size=1>'.$row&#1111;0].'</font>';
  			echo "<tr><td width=20% class=tableborder align=center><table><tr><td><b><a href="" class="under">".strtolower($row&#1111;3])."</a><b></td></tr></table></td>
			<td width=50% class=tableborder><table><tr><td><a href="reportview.php?pid=&#123;$row&#1111;2]&#125;" class="under" style="font-weight: bold;">$row&#1111;1]</a></td></tr></table></td>
			<td width=10% class=tableborder><table><tr><td>".replies()."</td></tr></table></td>
			<td width=20% class=tableborder><table><tr><td>$row&#1111;0]</td></tr></table></td></tr>";  
			
		&#125;
to highlight more

Code: Select all

<td width=10% class=tableborder><table><tr><td>".replies()."</td></tr></table></td>
here in this table

my db structure is i have users, posts and comments, i want to track the replies located in my comments table....

Posted: Wed Mar 02, 2005 5:28 am
by CoderGoblin
you don't have $postID (As far as I can tell you use $row[2]/$_GET['pid']) at a quick glance.

Remember what I said about defensive programming before in a different post. Add a check...

Code: Select all

$query = "SELECT comment FROM comments, posts, users
   WHERE comments.postID = posts.postID AND comments.firstname = users.firstname AND comments.postID=$postID";
   $result = @mysql_query($query);

   // Check here
   if (!$result) die(mysql_error());

   $num = mysql_num_rows($result);   
   if ($num >= 0)  echo "$num";
&#125;
.

Posted: Wed Mar 02, 2005 6:36 am
by pleigh
whoah!!!still cant solve my problem.....i don't know how to ask help but if anyone can, please reply....

Posted: Wed Mar 02, 2005 6:52 am
by smpdawg
I think that an important point is being missed here. Never do a SELECT * (or any other field names) if all you are trying to get is get the number of rows. You should always do a SELECT count(*) AS rowcount FROM table.

What is the difference? Think about it. With a SELECT * you are returning all the rows and columns as controlled by the WHERE clause. All of this data is being sent to PHP just to determine a row count. Why would you send what could be hundreds or thousands of rows and fields when all you want to know is the row count?

Every device can be thought of as a pipe and each pipe can only flow as much information as it can handle. This applies to memory, sockets, storage, databases, etc. In the case of the localhost pipe, they are sometimes limited to 10 megabit/second. While this may seem fast that actually equals about 1 megabyte of data per second. If you have enough queries running, the pipe fills and the web site slows down.

Knowing that let's go back to my original point. If the table has enough rows, you could be sending megabytes of data when the row count be returned in just a few characters. In the case of a new install of your scripts, the row count would be low and things would look OK. When the number of rows increases as a function of use you would be sending more and more data across that pipe and then the simple act of doing a row count gets slower and slower.

Then think about the secondary impact of a loose query. Why did a row count care what the user name was or any other data that was in the table? Why did the hard drive have to return those fields? Why did your database need to handle those fields? The answer to all of those questions is they don't because it is irrelevant to the act of counting rows. Each one of these things is a pipe and you are unnecessary filling each of them with data that never needed to be sent.

The end result of loose queries like this is that you will have a system that worked great to begin with but can grow to be intolerably slow if you have a lot of users and a lot of data. Then you will be scratching your head and trying to figure out what went wrong when you could have nipped it in the bud.

Never return a field that isn't used in a query. Never return rows that are unnecessary. It is a waste of resources.

Posted: Wed Mar 02, 2005 6:58 am
by pleigh
thanks a lot....how can i restructure my queries to fit your advise??

Posted: Wed Mar 02, 2005 7:07 am
by CoderGoblin
Good point smpdawg, I assumed that he was going to use the data returned, not just the row count.


You should also not use SELECT count(*), but SELECT count(unique_index_field) to save more "resources".

To answer the question though... Use the following:

Code: Select all

$query = "SELECT comment FROM comments, posts, users
   WHERE comments.postID = posts.postID AND comments.firstname = users.firstname AND comments.postID=$postID";

//  Show SQL Query
echo("Query=$query<hr />");
When you run the code you should now get the SQL query displayed. Is this what you were expecting ? If you run this command using SQL on the database (phpAdmin or whatever, not through your PHP code) does it work ?

If No then we need to track down why (at a guess the parameter is not set).

If Yes add this immediately after you set the $result:

Code: Select all

// Check here
   if (!$result) die(mysql_error());
This should show any sql query errors and terminate the script. If no error result then the SQL query is wrong (should have failed when running the command through SQL) on the database itself.

All we are trying to do is narrow down the problem. Imagine the the code as being steps. Perform these steps in turn and verify the program has the data you expect.

Posted: Wed Mar 02, 2005 7:14 am
by pleigh
problem is, i dont know where $postID will get its value, it is not yet declared

Code: Select all

comments.postID=$postID";

Posted: Wed Mar 02, 2005 7:14 am
by smpdawg
1. You need to come up with $postID.
It would appear that you could get $postID by doing this:

Code: Select all

$postID = $row&#1111;2];
2. Your original query is doing all sorts of joins that have nothing to do with returning a row count. Get rid of the extra garbage in your row count query. You most likely need something more like this.

Code: Select all

$rcQuery = "SELECT count(*) AS rowcount FROM comments WHERE postID = &#123;$postID&#125;";
$rcResult = @mysql_query($rcQuery);
3. Get the row count back from the query. There are many ways, this one should work.

Code: Select all

$rcRow = mysql_fetch_array($rcResult, MYSQL_NUM);
$RowCount = $rcRow&#1111;0];
Use the new $RowCount variable.

BTW - These all go beside each other inside of the loop that is walking the database and right before you need to display the row count. The narrative is just to explain what I am doing.

Posted: Wed Mar 02, 2005 7:24 am
by pleigh
nope...still not working... :cry:

Posted: Wed Mar 02, 2005 7:25 am
by CoderGoblin
Post your current PHP SELECT code and the result of echoing your query.

NOTE:

Code: Select all

$rcQuery = "SELECT count(postID) AS rowcount FROM comments WHERE postID = &#123;$postID&#125;";
$rcResult = @mysql_query($rcQuery);
Would save resources :wink: and be quicker. count(*) gets all fields which involves additional overhead in the same manner in which you should only ever return those fields you need.