How could I get a user's post number from one particular forum?
Example: I am User A. I make a post in Forum X. I also make a post in Forum Y. On my profile, it should say:
"Posts by User A in Forum X: 1".
In an ideal world, this would be used in a conditional, for displaying a filter link with xThreads - similar to this:
//postedthreads is the amount of threads in Forum X
<if $postedthreads > 1 then><a href="mods.mybb.com/profile/{$memprofile['uid']}">View {$memprofile['username']}'s Plugins on the MyBB Mods Site</a></if>
Would display as:
View Seabody's Plugins on the MyBB Mods Site
I think the query would be something like this (where X is the desired fid), though I'm no MySQL expert:
$postnum = $db->query("SELECT u.postnum FROM ".TABLE_PREFIX."user WHERE f.fid == X");
Did that make any sense whatsoever, or should I explain further?
Suppose if you want to count just the end user's post (the one who is viewing), then something like this would suit. If you want to count in some other way for all others as well, then the scenario would be different as you'd probably need to join two tables and query the data.
$uid = intval($mybb->user['uid']);
//Suppose that we are counting a user's post from fid 3
$query = $db->query("SELECT `uid`,`fid` FROM ".TABLE_PREFIX."posts WHERE uid='". $uid ."' AND fid=3");
//Now below would count the total posts of a user in forum id 3
$num_posts = $db->num_rows($query);
$db->free_result($query);
I just realized I made a few errors in my OP.
I want to count threads, not posts. I adjusted your query and it worked perfectly. Here's my final product, if anyone's interested:
$uid = intval($memprofile['uid']);
$query = $db->query("SELECT `uid`,`fid` FROM ".TABLE_PREFIX."threads WHERE uid='". $uid ."' AND fid=15");
$num_posts = $db->num_rows($query);
$db->free_result($query);
My only question, though, is what does the final line do?
It free's the result or query you can say. It's a good practice to release query once we've got the necessary data from it.
It's like eating chocolates. Once you eat the chocolate, what's the use of keeping the wrapper? (unless you're fancy about collecting them).
If may be wrong but, I assume there is not need to free the result if you are likely to do the query once more right after the first time.
@Seabody, just note that there can also be unapproved threads, which may not be supposed to be considered unless the current connected user is a moderator (normal/super/admin).
Thanks! So it's almost like making sure $query has no more information from this query hanging on to it?
(2012-12-23, 07:15 AM)Omar G. Wrote: [ -> ]@Seabody, just note that there can also be unapproved threads, which may not be supposed to be considered unless the current connected user is a moderator (normal/super/admin).
An excellent point. Would unapproved threads be counted in this, assuming a normal user was viewing the page running the query?
Yes, unapproved threads will be considered in your code. Use something like this:
$where = 'fid=15 AND uid=\''.$uid.'\'';
if(!$mybb->user['ismoderator'])
{
$where .= ' AND visible=\'1\'';
}
$query = $db->simple_select('threads', 'COUNT(tid) as threads', $where);
$num_threads = (int)$db->fetch_field($query, 'threads');
Also note that current user may not be able to see all forums (though the search system seems to be bugged if the current user is a moderator, it seems to work for normal users):
if($unviewable = get_unviewable_forums(true))
{
$where .= ' AND fid NOT IN ('.$unviewable.')';
}
I'm also interested in this, but I am not very skilled in "programming"/PHP/MyQSL yet.
I want to add the number of a user's threads in 3 different forums (+ all subforums) on the user's profile.
Something like:
Threads in Forum A = {number of threads by user in Forum A}
Threads in Forum B = ...
Threads in Forum C = ...
Is that possible to show on the profile?
Thank you
Why wouldn't you just do a fetch_field with a count on uid?
Count(uid) should be more efficient than num_rows as generally this can be grabbed from the index without hitting the data.
Should be marginal in small forums but every little bit of efficiency counts