Skip to main content ↑ ↓
Likes accounting Started by badmonkey · · Read 11375 times 0 Members and 1 Guest are viewing this topic. previous topic - next topic

Likes accounting

Hello everyone! Hope things are well for you!

The scenario: a monthly giveaway intended to boost board traffic and participation. The winner is based on total Likes received during the competition timeframe, let's say one month. Participation in the giveaway is optional, and staff members ineligible, so not every member will count. The giveaway will be recurring monthly. Participants could be entered into a member group. 

The question: how can the winner be determined in an automated fashion either including the participants or excluding certain members, whichever is easier?

Re: Likes accounting

Reply #1 –

The total likes per member over a given time period would be this query, just run this query it in your myphpadmin or adminer
Code: [Select]
SELECT
	lp.id_poster, lp.like_count,
	COALESCE(mem.real_name, m.poster_name) AS real_name, mem.posts
FROM (
	SELECT
		id_poster, COUNT(id_msg) AS like_count, MAX(id_msg) AS id_msg
	FROM elkarte_message_likes
	WHERE id_poster != 0 
		AND like_timestamp BETWEEN UNIX_TIMESTAMP("2022/12/01") AND UNIX_TIMESTAMP("2022/12/31 23:59:59")
	GROUP BY id_poster
	ORDER BY like_count DESC
	LIMIT 10
) AS lp
	INNER JOIN elkarte_messages AS m ON (m.id_msg = lp.id_msg)
	INNER JOIN elkarte_members AS mem ON (mem.id_member = m.id_member)
LIMIT 10
That will give the top ten for the month defined, in this case December.  That just a pure count of likes, so can be gigged :wink: e.g. two members simply post reply's and like each others messages.

You could also do a by unique likers on a topic in a given period, so if one member likes every post in a topic of another member, it would only count as one.  If you want that query let me know.
 

Re: Likes accounting

Reply #2 –

You're a genius spuds! You should write software or something.  :cheesy: 

Thanks man!

Re: Likes accounting

Reply #3 –

Quote from: badmonkey – Hello everyone! Hope things are well for you!

The scenario: a monthly giveaway intended to boost board traffic and participation. The winner is based on total Likes received during the competition timeframe, let's say one month. Participation in the giveaway is optional, and staff members ineligible, so not every member will count. The giveaway will be recurring monthly. Participants could be entered into a member group.

The question: how can the winner be determined in an automated fashion either including the participants or excluding certain members, whichever is easier?


Now there's an idea that can get out of hand in a hurry.. :woman_cartwheeling::partying_face::raised_hands::birthday: :cake::balloon::tada: :gift:  :rofl:

Might consider creating a new board, and confining the contest to that board, one that can be deleted later..  :smirk: :stuck_out_tongue_winking_eye:
// Deep inside every dilemma lies a solution that involves explosives //

Re: Likes accounting

Reply #5 –

Here's a brief followup. @Spuds your query code worked wonderfully! Thanks again!

Re: Likes accounting

Reply #6 –

Awesome  :cheesy: