Issue collecting just one result within a set in SQL

Discussion forum for Extension Writers regarding Extension Development.
Post Reply
User avatar
3Di
Registered User
Posts: 11872
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano - Frankfurt
Name: Marco
Contact:

Issue collecting just one result within a set in SQL

Post by 3Di » Sat Sep 23, 2017 8:14 am

I need help to sort out the last issue here, I am trying to collect the Top Poster of each month since a due timestart. All of the rest is working fine, results are sorted for post time etc..

The issue: collects all of the posters of that month(s) instead of the only one.
hall_1_months.png

Code: Select all

$sql = 'SELECT u.username, u.user_id, u.user_colour, u.user_avatar, u.user_avatar_type, u.user_avatar_width, u.user_avatar_height, MAX(u.user_type), p.poster_id, DATE_FORMAT(FROM_UNIXTIME(p.post_time), "%Y") AS year, DATE_FORMAT(FROM_UNIXTIME(p.post_time), "%m") AS month, MAX(p.post_time), COUNT(p.post_id) AS total_posts
	FROM ' . USERS_TABLE . ' u, ' . POSTS_TABLE . ' p
	WHERE u.user_id <> ' . ANONYMOUS . '
		AND u.user_id = p.poster_id
		AND ' . $this->db->sql_in_set('u.user_id', $this->tpotm->auth_admin_mody_ary(), true, true) . '
		AND ' . $this->db->sql_in_set('u.user_id', $this->tpotm->banned_users_ids(), true, true) . '
		AND (u.user_type <> ' . USER_FOUNDER . ')
		AND p.post_visibility = ' . ITEM_APPROVED . '
		AND p.post_time BETWEEN ' . (int) $board_start . ' AND ' . (int) $end_last_month . '
	GROUP BY u.user_id, month, year
	ORDER BY year DESC, month DESC, total_posts DESC';
$result = $this->db->sql_query($sql);
Thx.
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades

User avatar
canonknipser
Registered User
Posts: 1186
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Issue collecting just one result within a set in SQL

Post by canonknipser » Sat Sep 23, 2017 9:56 am

I'm afraid you need to create a "monthly loop" around your sql to pick up the top poster of every month.
In the loop, you need to calculate start and end date for the given period and put them in you where clause.
No need to calculate the month's ultimo, just calculate first day of current month and first day of following, WHERE-clause "post_time >= $current_month_start AND post_time < $following_month_start", after your sql execution you move following to current and recalculate following for the next month.
Greetings
Frank
phpbb.de support team member
English is not my native language
New arrival - Extensions and scripts for phpBB
no support via PM or mail

User avatar
3Di
Registered User
Posts: 11872
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano - Frankfurt
Name: Marco
Contact:

Re: Issue collecting just one result within a set in SQL

Post by 3Di » Sat Sep 23, 2017 7:41 pm

So adding a PHP montly loop right above the query, like a for loop?
Would be interesting to see a code example of sort, like a mockup. Thx though. :)
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades

User avatar
canonknipser
Registered User
Posts: 1186
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Issue collecting just one result within a set in SQL

Post by canonknipser » Sat Sep 23, 2017 8:50 pm

Yes, plain php ;)

A mockup:

Code: Select all


// assume start date, you  need to use proper phpBB values
$month_start = 11;
$year_start = 2007;
// its just today ;)
$current_date = gmmktime(0, 0, 0, 9, 22, 2017);


// create unix timestamp
$current_month_start = gmmktime(0, 0, 0, $month_start, 1, $year_start);
//calculate following month
$month_start += 1;
if ($month_start == 13)
{
	$month_start = 1;
	$year_start += 1;
}
// create unix timestamp
$following_month_start = gmmktime(0, 0, 0, $month_start, 1, $year_start);

while ($current_month_start < $current_date)
{
	$sql = 'Your pretty sql' .
		'WHERE p.post_time >= ' . (int) $current_month_start . ' AND p.post_time < ' . (int) $following_month_start ;
	// execute SQL, fetch the row and store result in an array
	$current_month_start = $following_month_start;
	//calculate following month
	$month_start += 1;
	if ($month_start == 13)
	{
		$month_start = 1;
		$year_start += 1;
	}
	// create unix timestamp
	$following_month_start = gmmktime(0, 0, 0, $month_start, 1, $year_start);

}
// 
Greetings
Frank
phpbb.de support team member
English is not my native language
New arrival - Extensions and scripts for phpBB
no support via PM or mail

User avatar
Steve
Registered User
Posts: 992
Joined: Tue Apr 07, 2009 7:48 pm
Location: UK ,up north!!
Name: Steven Clark
Contact:

Re: Issue collecting just one result within a set in SQL

Post by Steve » Sat Sep 23, 2017 9:15 pm

Querys in a loop 😞

User avatar
3Di
Registered User
Posts: 11872
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano - Frankfurt
Name: Marco
Contact:

Re: Issue collecting just one result within a set in SQL

Post by 3Di » Sat Sep 23, 2017 9:48 pm

In fact, we are already in a while loop.

I pushed to a dev branch the whole stuff, so to better see it:
https://github.com/3D-I/tpotm/blob/1823 ... #L116-L162
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades

User avatar
canonknipser
Registered User
Posts: 1186
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Issue collecting just one result within a set in SQL

Post by canonknipser » Sun Sep 24, 2017 10:12 am

So, instead of checking for the range of post_time in your WHERE clause, make a additional GROUP parameter with post_time aggregated on the year/month value, eg. FROM_UNIXTIME(post_time, '%Y%m')
But: FROM_UNIXTIME is MYSQL only, so not recommended in a extension published in CDB, I think.
I don't think there is a equivalent function in standard SQL, so for that you need to calculate Month & Year for a unix-timestamp outside the SQL
Greetings
Frank
phpbb.de support team member
English is not my native language
New arrival - Extensions and scripts for phpBB
no support via PM or mail

User avatar
3Di
Registered User
Posts: 11872
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano - Frankfurt
Name: Marco
Contact:

Re: Issue collecting just one result within a set in SQL

Post by 3Di » Sun Sep 24, 2017 3:10 pm

Is getting no where, I believe I have to face this with another approach.
Starting from emulating the FROM_UNIXTIME mysql in PHP

Code: Select all

	/**
	 * Get month and year of given timestamps
	 *
	 * @param	string	$timestamp	Unix timestamp
	 * @return	arrays	tring		Formatted string mm and YYYY (09 2017)
	 */
	public function dateformat_from_unixtimestamp($timestamp)
	{
		$month = new \DateTime();
		$month = $month->setTimestamp((int) $timestamp);

		$year = new \DateTime();
		$year = $year->setTimestamp((int) $timestamp);

		return [$month->format('m'), $year->format('Y')];
	}
usage

Code: Select all

	list($year, $month) = $this->dateformat_from_unixtimestamp(time());
	print_r($year);
	print_r($month);
let's see what I can do nextly, thanks. :)
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades

User avatar
canonknipser
Registered User
Posts: 1186
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Issue collecting just one result within a set in SQL

Post by canonknipser » Mon Sep 25, 2017 12:09 pm

Sorry, forget all my crap about the extra where clause, my suggestion with the monthly GROUP BY based on FROM_UNIXTIME(post_time) is already in your query, need to scroll to the right ... :oops:

Only my hint for non-Standard FROM_UNIXTIME is still standig, but you try to fix it ;)

But I believe creating a new function for that will not help you, because you need the function in your SQL, and you can't put php functions in a SQL, I'm afraid (and it may be giving you a glitch when you call it at the end of the very last second of the year, giving you the current month (12), but the following year (eg. 2018 instead of 2017) when just in the moment between the first and second call of SetTimestamp the date changes to first of january).
Another hint for the list language construct in your usage example: There is a change in the parameter order from php5 to php7 -> http://php.net/manual/en/function.list.php

Maybe you need a new column in the users table holding the date or timestamp of post data as a real datetime value and not a integer based on unix timestamp. You need to fill this in your migration and/or a cron for existing post, and during post insert. Then you can select it in your SQL with standard SQL function (YEAR, MONTH). Our choose a string value holding a ISO Date string ("YYYY-MM-DD") and work with SUBSTR in your SQL
Greetings
Frank
phpbb.de support team member
English is not my native language
New arrival - Extensions and scripts for phpBB
no support via PM or mail

User avatar
3Di
Registered User
Posts: 11872
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano - Frankfurt
Name: Marco
Contact:

Re: Issue collecting just one result within a set in SQL

Post by 3Di » Tue Sep 26, 2017 4:33 am

Well, I did re-wrote the code (see master) due to the addition of pagination/jumpbox and some decisions I took since the issue discussed here took to me more time than I expected to invest on this. And yes thanks for all of the good reading here Frank ;) Very usefull. Now I am fetching the DB as arrays and made the use of a foreach, that will be helpful in future to add some enhancement here. Who knows what would it be the users' feedback.

Thanks for all of your interest to you all guys, I for one I learnt a lot. :) :geek:

As it is now (the new code) will show the top poster of the month pretty styled (thx Steve) and a list of top posters ever (since the board start date till the last minute of the previous month) underneath, viewport is configurable in ACP.

I got a grab here.. I am quite satisfied at this point in time, I can say.
Attachments
hall_2_ever_top.png
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades

User avatar
canonknipser
Registered User
Posts: 1186
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Issue collecting just one result within a set in SQL

Post by canonknipser » Tue Sep 26, 2017 9:08 am

You're welcome, glad that I was a little bit helpful ;)
Greetings
Frank
phpbb.de support team member
English is not my native language
New arrival - Extensions and scripts for phpBB
no support via PM or mail

Post Reply

Return to “Extension Writers Discussion”

Who is online

Users browsing this forum: kinerity and 4 guests

cron