How to count user posts?

[ColdEdge]

Ok, so I been working on my own BBS, my question is how can I display number of user posts? I tried using SELECT COUNT(*) but it keeps failing.Here is the code for topic.php

$sql3 = "SELECT topic_id, topic_subject, topic_status, topic_cby FROM topics WHERE topics.topic_id = " . mysql_real_escape_string($_GET['id']);$result3 = mysql_query($sql3);while($row = mysql_fetch_assoc($result3)){							   #post status if 1 post locked else un-locked							   $topic_status = $row['topic_status'];							   $topic_locked_by = $row['topic_cby'];								if ($topic_status=="1") {echo '<div style="padding:10px;background-color:#FAE3E3;border:2px solid #B25959;text-align:center;">This topic was closed by '.$topic_locked_by.'. </div><br>';}}#Paginations$sql = "SELECT COUNT(*) FROM posts WHERE post_topic = ".$_GET['id']."";$result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);$r = mysql_fetch_row($result);$numrows = $r[0];$rowsperpage = 5;$totalpages = ceil($numrows / $rowsperpage);// get the current page or set a defaultif (isset($_GET['page']) && is_numeric($_GET['page'])) {   // cast var as int   $p = (int) $_GET['page'];} else {   // default page num   $p = 1;} // end if// if current page is greater than total pages...if ($p > $totalpages) {   // set current page to last page   $p = $totalpages;} // end if// if current page is less than first page...if ($p < 1) {   // set current page to first page   $p = 1;} // end if// the offset of the list, based on current page$offset = ($p - 1) * $rowsperpage;// get the info from the db$sql = "SELECT			topic_id,			topic_subject,			topic_cat		FROM			topics		WHERE			topics.topic_id = " . mysql_real_escape_string($_GET['id']);$result = mysql_query($sql);if(!$result){	echo 'The topic could not be displayed, please try again later.';}else{	if(mysql_num_rows($result) == 0)	{		echo 'This topic doesn′t exist.';	}	else	{		while($row = mysql_fetch_assoc($result))		{			$topicID = $row['topic_id'];			//display post data			echo '<div style="clear:both;height:10px;"></div>';								echo '<div style="width:900px;height:30px;margin:0 auto;">';			echo '<div style="width:280px;height:30px;display:block;float:right;text-align:right;">';			echo '<a href="newtopic.php?cid='.$row['topic_cat'].'&ref_id='.$row['topic_id'].'" style="font-size:14px;margin:4px;" class="minibutton"><span>New Topic</span></a>';			echo '<a href="index.php?func=addreply&topic='.$row['topic_id'].'" style="font-size:14px;" class="minibutton"><span>Add Reply</span></a>';			echo '</div>';			echo '</div>';			echo '<div style="clear:both;height:10px;"></div>';			echo '<div id="subject"><a href="topic.php?id=' . $row['topic_id'] . '">' . $row['topic_subject'] . '</a></div>'; #e1e7f5 or 2e51a2			//fetch the posts from the database			$posts_sql = "SELECT						posts.post_topic,						posts.post_content,						posts.post_date,						posts.post_by,						posts.post_id,						users.user_id,						users.user_name,						users.user_rank,						users.user_level,						users.user_signature,						users.user_thumb,						users.user_avatar					FROM						posts					LEFT JOIN						users					ON						posts.post_by = users.user_id					WHERE						posts.post_topic = " . mysql_real_escape_string($_GET['id'])." LIMIT $offset, $rowsperpage";									$posts_result = mysql_query($posts_sql);						function adminTools() {						$query3356 = mysql_query("SELECT * FROM users WHERE user_id='".$_SESSION['user_id']."'");			$row = mysql_fetch_array($query3356);			$permission = $row['user_level'];						if($permission > 0) {						echo '<div style="padding:8px;background-color:#fae7af;border-bottom:1px solid #cdcdcd;height:30px;">';			echo '<a href="index.php?func=admin&do=lockthread&topic='.$row['topic_id'].'&sessid='.$sessid.'-'.md5($ncObjtoken1).'" style="margin:4px;float:left;" class="minibutton"><span>Close Thread</span></a>';			echo '<a href="index.php?func=admin&do=stickthread&topic='.$row['topic_id'].'&sessid='.$sessid.'-'.md5($ncObjtoken1).'" style="margin:4px;float:left;" class="minibutton"><span>Stick Thread</span></a>';			echo '<a href="index.php?func=admin&do=nosmiliesthread&topic='.$row['topic_id'].'&sessid='.$sessid.'-'.md5($ncObjtoken1).'" style="margin:4px;float:left;" class="minibutton"><span>Disable Smilies</span></a>';			echo '</div>';			echo '<div style="clear:both;height:20px;"></div>';						} elseif($permission < 1) {			}			}						adminTools();			if(!$posts_result)			{				echo '<tr><td>The posts could not be displayed, please try again later.</tr></td></table>';			}			else			{							$i = 1;				while($posts_row = mysql_fetch_assoc($posts_result))				{									$query33413 = "SELECT COUNT(*) FROM posts WHERE post_by = ".$posts_row['user_id']."";					$row33413 = mysql_query($query33413);					$a31 = $row33413;										$text = parsebbCode($posts_row['post_content']);					$isAdmin = $posts_row['user_level'];					echo '					<div style="width:900px;height:auto;background-color: #e7eaef;">';					if($isAdmin > 0) {					echo '<div style="width:116px;float:left;background-color: #fae3e3;padding:12px;color:#634c55;font-size:11px;">					<a href="usercard.php?height=340&width=440&uid='.$posts_row['user_id'].'" style="color:#ff0000;" class="thickbox">' . $posts_row['user_name'] . '</a>					</div>					<div style="width:736px;float:right;background-color: #fae3e3;padding:12px;color:#634c55;font-size:11px;">							' . date('D M d, Y H:i a', strtotime($posts_row['post_date'])) . '<a href="#post'.$posts_row['post_id'].'" name="post'.$posts_row['post_id'].'" rel="nofollow" style="float:right;padding:-2px 4px;"># '.$i.'</a>					</div>					';					} elseif($isAdmin < 1) {					echo '<div style="width:116px;float:left;background-color: #c6dfc3;padding:12px;color:#634c55;font-size:11px;">					<a href="usercard.php?height=340&width=440&uid='.$posts_row['user_id'].'" style="color:#634c55;" class="thickbox">' . $posts_row['user_name'] . '</a>					</div>					<div style="width:736px;float:right;background-color: #c6dfc3;padding:12px;color:#634c55;font-size:11px;">							' . date('D M d, Y H:i a', strtotime($posts_row['post_date'])) . '<a href="#post'.$posts_row['post_id'].'" name="post'.$posts_row['post_id'].'" rel="nofollow" style="float:right;padding:-2px 4px;"># '.$i.'</a>					</div>					';					}					echo'					<div style="clear:both;"></div>					<div style="float:left;width:116px;background-color: #e7eaef;">					<div style="width:120px;float:left;background-color: #e7eaef;padding:10px;">					<p style="font-size:11px;">' . $posts_row['user_rank'] . '</p>					<div style="clear:left;height:3px;"></div>					<a href="member.php?uid='.$posts_row['user_id'].'"><img src="' . $posts_row['user_thumb'] . '" class="user_avatar"/></a>					<div style="clear:left;height:6px;"></div>					Posts: <a href="search.php?func=posts&uid='.$posts_row['user_id'].'&o=simple">'.$a31.'</a>					<div style="clear:left;height:6px;"></div>					';					$user_id = $posts_row['user_id'];					$query345 = mysql_query("SELECT * FROM useronline WHERE user_id='".$user_id."'");					$rows = mysql_num_rows($query345);					if($rows==0) {					$online_status = '<p style="font-size:11px;">Offline</p>';					} else {					$online_status = '<p style="font-size:11px;color:#008000;">Online</p>';					}					echo ''.$online_status.'';					echo'					<div style="clear:right;"></div>					</div>					</div>					<div style="width:740px;float:right;background-color: #eff1f3;min-height: 240px;padding:10px;">							<p style="white-space: pre;line-height:0.7em;">' . $text . '</p>							<div style="clear:both;height:6px;"></div>							<div style="width:740px;border-top:1px dotted #313131;"></div>							<div style="clear:both;height:6px;"></div>							<p>' . $posts_row['user_signature'] . '</p>					</div>					<div style="clear:both;"></div>					<div style="width:900px;height:30px;background-color:#c6ccd4;margin:0 auto;">';					$user_id = $posts_row['user_id'];					if($user_id != $_SESSION['user_id']) {					echo '<a href="report.php?func=report&hpcServlet='.sha1(md5($ncObjtoken2)).'&tid='. $topicID . '&post=' . $posts_row['post_id'] . '&sessid='.sha1($sessid).'&rsb=['.$ncObjtoken1.'-'.$ncObjtoken2.'-'.$_SESSION['user_id'].']" style="float:right;margin:4px;" class="minibutton"><span>Report</span></a>';					echo '<a href="post.php?func=reply&tid='.$topicID.'&pid='.$posts_row['post_id'].'&quote='.$posts_row['post_id'].'&sessid='.$sessid.'" style="float:right;margin:4px;" class="minibutton"><span>Quote</span></a>';										echo '<a href="newreply.php?func=reply&tid='.$topicID.'&pid='.$posts_row['post_id'].'&sessid='.$sessid.'" style="float:right;margin:4px;" class="minibutton"><span>Reply</span></a>';					} else {					echo '<a href="delete.php?tid='. $topicID . '&post=' . $posts_row['post_id'] . '&height=88&width=380&cf=keep&sessid='.$sessid.'-'.$ncObjtoken1.'" style="float:right;margin:4px;" class="thickbox" id="minibutton"><span>Delete</span></a>';					echo '<a href="post.php?func=edit&tid='. $topicID . '&pid=' . $posts_row['post_id'] . '&sessid='.$sessid.'-'.$ncObjtoken2.'" style="float:right;margin:4px;" class="minibutton"><span>Edit</span></a>';					}										echo '					</div>					</div>					<div style="clear:both;height:20px;"></div>';										$ifAdmin = $posts_row['user_level'];							$i++;				}			}			if(!$_SESSION['signed_in'])			{				echo '<div style="clear:both;height:20px;"></div>';				echo 'You must be <a href="signin.php">signed in</a> to reply. You can also <a href="signup.php">sign up</a> for an account.<div style="clear:both;height:10px;"></div>';			}			else			{															//show reply box				if ($p < 1) {			   // set current page to first page				$p = 1;				} // end if				echo '					<form method="post" action="reply.php?id=' . $row['topic_id'] . '&p='.$p.'">						<textarea name="reply-content" style="width:100%" rows="5" cols="20" id="elm1"></textarea>						<div style="clear:both;height:10px;"></div>						<input type="submit" value="Post Reply" />					</form>					<a href="attachui.php?act=controls&TB_iframe=true" class="thickbox" class="minibutton"><span>Add File</span></a>					<div style="clear:both;height:20px;"></div>';			}		}	}}/******  build the pagination links ******/// range of num links to show$range = 6;// if not on page 1, don't show back linksif ($p > 1) {   // show << link to go back to page 1   echo " <a href='{$_SERVER['PHP_SELF']}?id=".$_GET['id']."&page=1' class='minibutton'><span><<</span></a> ";   // get previous page num   $prevpage = $p - 1;   // show < link to go back to 1 page   echo " <a href='{$_SERVER['PHP_SELF']}?id=".$_GET['id']."&page=$prevpage' class='minibutton'><span><</span></a> ";} // end if// loop to show links to range of pages around current pagefor ($x = ($p - $range); $x < (($p + $range) + 1); $x++) {   // if it's a valid page number...   if (($x > 0) && ($x <= $totalpages)) {	  // if we're on current page...	  if ($x == $p) {		 // 'highlight' it but don't make a link		 echo " <a href='' class='minibutton'><span>$x</span></a> ";	  // if not current page...	  } else {		 // make it a link		 echo " <a href='{$_SERVER['PHP_SELF']}?id=".$_GET['id']."&page=$x' class='minibutton'><span>$x</span></a> ";	  } // end else   } // end if} // end for// if not on last page, show forward and last page linksif ($p != $totalpages) {   // get next page   $nextpage = $p + 1;	// echo forward link for next page   echo " <a href='{$_SERVER['PHP_SELF']}?id=".$_GET['id']."&page=$nextpage' class='minibutton'><span>></span></a> ";   // echo forward link for lastpage   echo " <a href='{$_SERVER['PHP_SELF']}?id=".$_GET['id']."&page=$totalpages' class='minibutton'><span>>></span></a> ";} // end if}/****** end build pagination links ******/

And also one of my other question if there is more then 5 posts per page the pagination system will display rest on the 2nd page using ?page=2 for example. But when you go to page to the $i keeps showing #1 instead of showing #6 for post number. How would I fix this?Thanks in advance ^^

[ColdEdge]

Btw, since the code is so long here is the part that must count the number of posts made by the user

$query33413 = "SELECT COUNT(*) FROM posts WHERE post_by = ".$posts_row['user_id']."";					$row33413 = mysql_query($query33413);					$a31 = $row33413;

[Ixzion]

Why not use mysql_num_rows on your query instead of using count?http://us2.php.net/manual/en/function.mysql-num-rows.php

$query33413 = "SELECT * FROM posts WHERE post_by = ".$posts_row['user_id']."";$row33413 = mysql_query($query33413);$numposts = mysql_num_rows($row33413); // Returns the number of rows generated from your query

[ColdEdge]

A thanks Ixzion it works, but what can I do with getting the #post to work?

[Ixzion]

I'm not near my main computer, so I can't give out code. However, I do know that you are declaring

$i = 1; // This is being declared the starting value every timewhile($posts_row = mysql_fetch_assoc($posts_result))				{									$query33413 = "SELECT COUNT(*) FROM posts WHERE post_by = ".$posts_row['user_id']."";					$row33413 = mysql_query($query33413);

each time the page is displayed. So the post number goes back to 1 every time.

[ColdEdge]

Yes, every time I move to next page Post # is reset back to 1so it goesPosts #1Posts #2Posts #3Posts #4Posts #5And on with &page=2 it is reset back to Posts #1Posts #2Posts #3Posts #4Posts #5This keeps happening for every page &page=## be it 100 or 32 it dosen't matter still same problem

[Gyohdon]

Yes, every time I move to next page Post # is reset back to 1so it goesPosts #1Posts #2Posts #3Posts #4Posts #5And on with &page=2 it is reset back to Posts #1Posts #2Posts #3Posts #4Posts #5This keeps happening for every page &page=## be it 100 or 32 it dosen't matter still same problem

I'm pretty new to coding, but this is what your post made me think about. Just trying to help a bit.

<?php$page = $_GET['page'];$allpost = ($page-1)*5// I don't know if that actually works as a formula, but what I tried is if page = 2,// then it gives you 5, if page = 4 it gives you 15, etc$post1 = $allpost + 1$post2 = $allpost + 2$post3 = $allpost + 3$post4 = $allpost + 4$post5 = $allpost + 5echo "Post #" . $post1;echo "Post #" . $post2;echo "Post #" . $post3;echo "Post #" . $post4;echo "Post #" . $post5;?>

[Ixzion]

Actually, I think your reasoning was on the right path.To put it into perspective a bit, using the OP's code:

$i = ($p - 1) * $rowsperpage; // On page 1, it'll be (1-1) * 5, which will equal 0. Unless we want the first #post to be #0, we need to check for and correct this.if ($i < 1) {$i = 1; // If the value of $i is less than 1 (which means we're on the first page), we set the post number to 1}while($posts_row = mysql_fetch_assoc($posts_result))				{									$query33413 = "SELECT COUNT(*) FROM posts WHERE post_by = ".$posts_row['user_id']."";					$row33413 = mysql_query($query33413);

[ColdEdge]

I got it to work with this

if($_GET['page'] == '' OR $_GET['page'] == '1') {$i = 1;} elseif($_GET['page'] > 1) {$ou = $_GET['page'];$i = ($ou - 1) * $rowsperpage + 1;}

[Synook]

The reason why your original query wasn't doing what you wanted it to do is because it is still a table that is generated, with a single column named "COUNT(*)". So you have to retrieve it thus.

$query33413 = "SELECT COUNT(*) FROM posts WHERE post_by = ".$posts_row['user_id']."";$row33413 = mysql_fetch_assoc(mysql_query($query33413));$a31 = $row33413["COUNT(*)"];