Auto Counting Additional Statistics

   (0 reviews)

Auto Counting Additional Statistics

Tutorial Created By: isoldehn
Tutorial Permissions: Please do not re-post this tutorial else where, instead link someone to this thread.

- Additionally, you are NOT required to credit me if you use these codes on your board BUT I do wish for you to share this with those that ask about it if they ever do (share the knowledge, be kind).
Tutorial Needs: Overall, the ability to copy and paste.
- Knowledge Of MyBB Templates 
- FTP/File Access To Web Space

- Knowledge On Editing Core Files/Creating MyBB Pages OR Usage of PHP In Templates / Complex Templates Plugin

* Using the above linked plugin allows you to inject PHP straight into templates without editing core files or creating new pages in MyBB.

Lets begin to process...

There are MANY capabilities within this guide. Therefor I am expecting you to already know how to:

• Insert scripts into core files (where you need them).
  OR
• Create a new page specifically for all the scripting you are about to do.
  OR
• Make use of the plugin linked above for inputting the script directly into a template.

I will not be explaining how to do any of the above but instead giving you strict scripts for purpose. If there are any questions regarding the above information you can quickly find information via MyBBs support forum where lots of documentation can be found on the above information.

MyBB stores all database information via MySQL. We will be pulling all the information from said storage!

Alright I'm ready to see the base code for counting accounts in a usergroup.

This is the very base, so look at it in all its basic poop glory.

$countquery = $db->fetch_field(
	$db->query("
		SELECT COUNT(*) as countquery
		FROM ".TABLE_PREFIX."users
		WHERE ".TABLE_PREFIX."users.usergroup IN (#)
	"), 'countquery');

1. $countquery = Your variable, this will be the input for templates when finished. You might stick this in Index Templates -> index or you may be putting this in a specific template for a new page depending on where/how you choose to create the script.
2. SELECT COUNT(*) as countquery = You are defining what this is counting.
3. FROM ".TABLE_PREFIX."users = We are now grabbing the users table and storing its information.
4. WHERE ".TABLE_PREFIX."users.usergroup IN (#) = Defining the usergroup row as well as the groups GID number. If using multiple groups in one query it would be separated by commas (#, #, #).
5. 'countquery' = We're ending our defined counting.

I'm actually looking to count profile field inputs per groups instead.

If you are looking to count specific profile field inputs you would do so with this base instead.

$countquery = $db->fetch_field(
    $db->query("
        SELECT COUNT(*) AS countquery
        FROM ".TABLE_PREFIX."users u
        LEFT JOIN ".TABLE_PREFIX."userfields uf
        ON (u.uid=uf.ufid)
        WHERE u.usergroup IN (#) AND uf.fid#='OPTION'
    "), 'countquery');

Besides what is explained previously, there are a few new additions here.

1. FROM ".TABLE_PREFIX."users u = Usergroup is in the users table, we need to add it to the FROM section so we can also select fields next.
2. LEFT JOIN ".TABLE_PREFIX."userfields uf = Joining the user fields with the users table.
3. ON (u.uid=uf.ufid) = I'm not really sure but my browsing of the internet got me this bit that makes it work.
4. WHERE u.usergroup IN (#) AND uf.fid#='OPTION' = New approach to usergroup specification while also requiring that it matches profile field option. uf.fid3='Male' is an example.

I'm rather looking to count posts from a board.

Maybe you have specific boards for IC threads and would prefer your statistics to show only threads/posts in character. Here is a step towards achieving that.

$countquery = $db->fetch_field(
	$db->query("
		SELECT COUNT(*) as countquery
		FROM ".TABLE_PREFIX."posts
		WHERE fid IN (#)
	"), 'countquery' );

1. FROM ".TABLE_PREFIX."posts = This is where you will gain the counts of posts. If you're looking for threads do threads instead.
2. WHERE fid IN (#) = The boards FID number.

How about counting a specific groups post totals.

Maybe your groups battle for the largest post/thread amounts? See whom has the highest!

$counterquery = $db->query("
	SELECT COUNT(*) AS counterquery
	FROM ".TABLE_PREFIX."users u
	LEFT JOIN ".TABLE_PREFIX."posts
	ON (u.uid=".TABLE_PREFIX."posts.uid)
	WHERE u.usergroup IN (#)
"); 

1. $counterquery = The variable for the counter script.
2. LEFT JOIN ".TABLE_PREFIX."posts = You're choosing posts, if you would rather count threads replace posts with threads.
3. ON (u.uid=".TABLE_PREFIX."posts.uid) = Combing users to their posts, again if you would rather count threads replace posts with threads.

TIP, if you would like to count posts in say the last 30 days time you would change the WHERE clause to something along these lines: WHERE dateline > ".TIME_NOW." - 2592000 AND u.usergroup IN (#)

---

That is all I have for this guide. This guide was meant to give a push in the right direction for those willing to learn but it is not completely simplified. If questions arise you are free to create a support thread in the Coding Help & Requests board.
If you become creative you can do so much more than just the basics of what I have offered here. Dig deep into the internet, search boards and places for more kicks in the right direction. Auto counting can be as simple as you want it to be if you piece things together correctly.