Last & present month post count in Profile

All web design discussion, including Ultimate Quiz MOD support.

Moderator: CricketMX Forum Moderators

Post Reply
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

I have the Posts this Month (View Profile) MOD installed but I also needed it to display the previous months post count in a members profile which I had succeeded doing and was all working perfectly until we hit January, a new year. For some reason, Last Months post count is now showing the total post count for the past 12 months!! It should just be for December.

As a temporarily measure, I've inserted a quick month check so it displays to members in the Last Month posts field: Temporarily unavailable.

Anyhow, here's the code:

File: ncludes/usercp_viewprofile.php

Code: Select all

    // First, work how many posts they've made this month
    $current_time = time();
    $this_months_posts = 0;
    $year = date('Y', $current_time);
    $month [0] = mktime (0,0,0,1,1, $year);
    $month [1] = $month [0] + 2678400;
    $month [2] = mktime (0,0,0,3,1, $year);
    $month [3] = $month [2] + 2678400;
    $month [4] = $month [3] + 2592000;
    $month [5] = $month [4] + 2678400;
    $month [6] = $month [5] + 2592000;
    $month [7] = $month [6] + 2678400;
    $month [8] = $month [7] + 2678400;
    $month [9] = $month [8] + 2592000;
    $month [10] = $month [9] + 2678400;
    $month [11] = $month [10] + 2592000;
    $month [12] = $month [11] + 2592000;
    $arr_num = ( date('n')-1);
    $time_thismonth = $month[$arr_num];
    $sql = "SELECT count(post_id) as monthly_posts
    FROM " . POSTS_TABLE . "
    WHERE poster_id = {$profiledata['user_id']}
    AND post_time > '" . intval($time_thismonth) . "'";
    if(! $result = $db->sql_query($sql) )
    {
       message_die(GENERAL_ERROR, 'Could not obtain last months postcount.', '', __LINE__, __FILE__, $sql);
    }
    $row = $db->sql_fetchrow($result);
    $this_months_posts = $row['monthly_posts'];

    $db->sql_freeresult($result);

    // Now then, how many did they make last month?
    $current_time = time();
    $last_months_posts = 0;
    $year = date('Y', $current_time);
    $month [0] = mktime (0,0,0,1,1, $year);
    $month [1] = $month [0] + 2678400;
    $month [2] = mktime (0,0,0,3,1, $year);
    $month [3] = $month [2] + 2678400;
    $month [4] = $month [3] + 2592000;
    $month [5] = $month [4] + 2678400;
    $month [6] = $month [5] + 2592000;
    $month [7] = $month [6] + 2678400;
    $month [8] = $month [7] + 2678400;
    $month [9] = $month [8] + 2592000;
    $month [10] = $month [9] + 2678400;
    $month [11] = $month [10] + 2592000;
    $month [12] = $month [11] + 2592000;
    $arr_num = ( date('n')-2);
    $time_lastmonth = $month[$arr_num];
    $sql = "SELECT count(post_id) as lastmonthly_posts
    FROM " . POSTS_TABLE . "
    WHERE poster_id = {$profiledata['user_id']}
    AND post_time > '" . intval($time_lastmonth) . "'";
    if(! $result = $db->sql_query($sql) )
    {
       message_die(GENERAL_ERROR, 'Could not obtain last months postcount.', '', __LINE__, __FILE__, $sql);
    }
    $row = $db->sql_fetchrow($result);

    $last_months_posts = $row['lastmonthly_posts'] - $this_months_posts ;
    if ( $time_lastmonth = 12 )
    {
    $last_months_posts = 'Temporarily unavailable';
    }
    if ( $last_months_posts <0 )
    {
    $last_months_posts = 0 ;
    }

    $db->sql_freeresult($result);

This part: $arr_num = ( date('n')-1);, returns the current month value. In the second part, the value is: 2, to obtain the previous month. I then used
$last_months_posts = $row['lastmonthly_posts'] - $this_months_posts ; to obtain the previous months post count.

Any help greatly appreciated! :D
Last edited by quahappy on Sun Mar 22, 2009 5:14 pm, edited 1 time in total.
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

To obtain last months number of posts, use this:

Code: Select all

$current_time = time();
$this_month = date('n', $current_time); 
$this_year = date('Y', $current_time);

    if( $this_month == 1 )
    {
        $this_year = $this_year - 1; 
        $this_month = 13;
    }

$start_date = mktime(0, 0, 1, ($this_month - 1), 1, $this_year, 0); // First day of last month in unix timestamp
$last_day_of_last_month = date('t', $start_date);
$end_date = mktime(23, 59, 59, ($this_month - 1), $last_day_of_last_month, $this_year, 0); // Last day of last month in unix timestamp

$sql = "SELECT COUNT(post_id) AS lastmonthly_posts
        FROM " . POSTS_TABLE . "
        WHERE poster_id = " . $profiledata['user_id'] . "
        AND post_time > $start_date 
        AND post_time < $end_date";

$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);

$total_posts_last_month = $row['lastmonthly_posts']; // The final product, posts last month

$db->sql_freeresult($result);
 
Basically, the code above will be able to find out for you the time of the first day of last month and the time of the last day of last month, and then use it within the SQL query.

I even tested it for you on CMX, with these results :)
I just worked out the total posts though, I took out the line that makes it specific to a certain user. (in other words, I removed the WHERE poster_id = " . $profiledata['user_id'] . " line)
Begin....
Last day of last month (a date): 31
Start: 1228111201 | End: 1230789599
Posts: 754
754 posts for December sounds about right :)
If you plug those numbers into http://www.unixtimestamp.com/index.php you can see that 1228111201 and 1230789599 relate to the morning of December 1 and the midnight of December 31 respectively.


If you want to use a similar piece of code for the "this month" stats, then use this:

Code: Select all

$current_time = time();
$this_month = date('n', $current_time); 
$this_year = date('Y', $current_time);

$start_date = mktime(0, 0, 1, $this_month, 1, $this_year, 0);
$end_date = $current_time;

$sql = "SELECT COUNT(post_id) AS thismonthly_posts
        FROM " . POSTS_TABLE . "
        WHERE poster_id = " . $profiledata['user_id'] . "
        AND post_time > $start_date
        AND post_time < $end_date";

$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);

$total_posts_this_month = $row['thismonthly_posts']; // Total posts this month

$db->sql_freeresult($result); 



To make use of the results, just use $total_posts_last_month and $total_posts_this_month respectively. I didn't test the code with $profiledata['user_id'] but I am pretty sure it will work. You might need to tweak it slightly to use it with the rest of your code (if you need any help with that, just ask).
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

Thank you so much for your in-depth reply battye! Works perfectly.

I had asked this over at phpBB.com but sadly know that phpBB2 forums are getting less replies to queries.

Thanks again. :D
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

Not a problem. :) I've moved this topic to the Web Design forum.
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

Hi Battye,

I've recently done a "test" transfer of my phpBB2 forum to phpBB3 and in the process of going through everything with a fine tooth comb to see what needs changing, adding, etc, etc before I perform the final transfer (quite a few alterations needed :( ), Not even touched the surface yet - so many options.....

Could you tell me what phpBB3 files I need to edit and code required please to display in a members profile: Last months post count / Present months post count ?

Thanks in advance for any help. :D
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

Hi,

Is it okay if you give me FTP access? I can do this for you if you want.
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

Sure thing. Do you want to do a test run at my "phpBB3" test site first? This is where I performed a test transfer of all forums, threads and posts from phpbb2 to phpbb3 as previously mentioned in my post above.

I don't have an actual date of when the "final" transfer from phpbb2 to phpbb3 will be. When I do have a date, I'm probably looking at about 4 - 7 days getting everything transferred from phpbb2 to phpbb3 along with all the necessary changes required because of time issue (5 kids and wife to feed lol) before "new" phpbb3 forum goes live.

PM sent as well. ;)
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

Thanks, got the PM :)

I will do it on the test forum for you. At the same time I will also write the instructions on how to do it, so you know how to do it yourself if you need to in the future. Are you going to eventually merge the testing board into the live board, or will the live board be like a fresh install of phpBB3? I ask because you might be able to simply copy the files in question across if you intend to use new files. But this is why I will include instructions as well, that way you can still do it whichever way you originally intended.

Hope that makes sense :oops:
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

The live board will be another fresh install of phpBB3 with forums and everything transferred from phpBB2 to it (using phpBB3 Conversion method) and this "live" forum directory will be /forum (as it is now with phpBB2). The present phpBB3 install I've done is now in a directory called /phpbb3testing which I will use to perform all testing of MOD's and "tweaks" (i.e. UQM, etc.).

In phpbb3testing directory, five files have been modified. These are

memberlist.php
posting.php
viewtopic.php

The above have had code added for a MOD I won't mention as already got into trouble from phpBB.com Support Team for helping another member... but I'm guessing you won't need to touch those files. The other two modified ones are overall_header.html and common.css (fiddled with to get logo sorted).

When you're done (and thank you by the way!) at /phpbb3testing and when I've installed the live phpBB3 forum I'll copy the required files from phpbb3testing to /forum directory ;)

By the way, I'm really missing the eXtreme Styles MOD from phpBB2 as that MOD allows files to be edited online which was extremely handy for quick code changes. Downloading, modifying then uploading file(s) via FTP is a little slower for phpBB3. lol.
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

You can edit files online in phpBB3 under Styles->Templates->Edit->Select file

Here is the blurb:
Here you can edit your template set directly. Please remember that these edits are permanent and cannot be undone once submitted. If PHP can write to the template files in your styles directory any changes here will be written directly to those files. If PHP cannot write to those files they will be copied into the database and all changes will only be reflected there. Please take care when editing your template set, remember to close all replacement variable terms {XXXX} and conditional statements.
So if your PHP configuration allows it the files will be written to, which sounds alright to me. But if your PHP configuration doesn't allow that, then it will be stored in the database which wouldn't be all that handy - as when you transferred the files from the test board to the live board then the changes would be lost.

I would keep doing it via FTP for the time being (I have never used the phpBB template editor, ever), and maybe start using the editor once you are using the live board.

The MOD that you won't mention: I think I know the one you are talking about. My suggestion is to just use it responsibly. Obviously there are small risks associated, but if you limit the number of people with access and you have an important use for it then there shouldn't be any problems. The quote from the MOD author sums it up :)
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

lol. I had even quoted for that "MOD" in a post over at phpbb.com the developer's warning but I think it was the fact I had included a link to the MOD was the part I had overstepped phpbb,com posting rules lol. That "MOD" is set-up at my site so only I can use it as I know too well of the implications. ;)

I knew that you could edit templates but assumed it was only html files and not php but reading what you put makes sense. Ive not really gone through this area in greater detail. I'll stick to FTP from now on. :D

Think this thread could probably do with renaming to: "Members previous month post count utility" or something lol.
If you don't ask...
User avatar
battye
Site Admin
Site Admin
Posts: 14391
Joined: Sun Jan 11, 2004 8:26 am
Location: Australia
Contact:

Okay, done. You can check it in your profile at the test board.

I did this:

Opened memberlist.php

FIND

Code: Select all

		$template->assign_vars(array(
			'L_POSTS_IN_QUEUE'	=> $user->lang('NUM_POSTS_IN_QUEUE', $member['posts_in_queue']),
AFTER ADD

Code: Select all

			'LAST_MONTHS_POSTS' => $last_months_result['lastmonthly_posts'],
			'THIS_MONTHS_POSTS' => $this_months_result['thismonthly_posts'],
BEFORE ADD (so before the $template->assign_vars part)

Code: Select all

		$current_time = time();
		$this_month = date('n', $current_time); 
		$this_year = date('Y', $current_time);

		    if( $this_month == 1 )
    		{
        		$this_year = $this_year - 1; 
        		$this_month = 13;
    		}

		$start_date = mktime(0, 0, 1, ($this_month - 1), 1, $this_year, 0); // First day of last month in unix timestamp
		$last_day_of_last_month = date('t', $start_date);
		$end_date = mktime(23, 59, 59, ($this_month - 1), $last_day_of_last_month, $this_year, 0); // Last day of last month in unix timestamp

		$last_months_sql = $db->sql_query("SELECT COUNT(post_id) AS lastmonthly_posts
        FROM " . POSTS_TABLE . "
        WHERE poster_id = " . $user_id . "
        AND post_time > $start_date 
        AND post_time < $end_date");
		$last_months_result = $db->sql_fetchrow($last_months_sql);

		$this_months_sql = $db->sql_query("SELECT COUNT(post_id) AS thismonthly_posts
        FROM " . POSTS_TABLE . "
        WHERE poster_id = " . $user_id . "
        AND post_time > $end_date");
		$this_months_result = $db->sql_fetchrow($this_months_sql);
So as you can see, almost identical to the code I gave for phpBB2. There is probably a way to condense that into one query - in fact I'm sure I read how to do it a few years ago in a PHP/MySQL book but as I have never had a use for it, I subsequently forgot it :oops: :cry:


OPEN /styles/prosilver/templates/memberlist_view.html

FIND

Code: Select all

			<dt>{L_JOINED}:</dt> <dd>{JOINED}</dd>
AFTER ADD

Code: Select all

			<dt>Posts made last month:</dt> <dd>{LAST_MONTHS_POSTS}</dd>
			<dt>Posts made this month:</dt> <dd>{THIS_MONTHS_POSTS}</dd>
And that's it. :)

Regarding the "MOD", I imagine it was not because of the link itself, but because of the actual MOD that is was linking to.

I tried renaming the topic to add "utility", but it was too long. So I left it how it is now 8)
CricketMX.com in 2022: Still the home of bat's, rat's and other farmyard animals!

"OK, life [as you chose to define it] repeats until there are no more lessons to be learned." - nrnoble (June 12, 2005)
"the new forum looks awesome, it's getting bigger & better" - p2p-sharing-rules (11 Jan, 2008)
"Looks like CMX is not only getting bigger...but, also getting better!!" - moongirl (14 Dec, 2007)
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

Brilliant battye! Can't begin to tell you how grateful I am. :prayer: :D

Re: That "MOD". Last tiem I make that error again posting at phpbb.com LOL.

Oh, and a warm welcome to my phpbb3testing site. ;)
If you don't ask...
User avatar
quahappy
Happy, not Quappy
Happy, not Quappy
Posts: 2266
Joined: Fri Oct 10, 2008 8:56 pm
Location: South Yorkshire, UK

Topic renamed to: Last & present month post count in Profile
If you don't ask...
Post Reply