Notes with Ravinder on Badging System Development Part II


 
Thread Tools Search this Thread
Top Forums Web Development Notes with Ravinder on Badging System Development Part II
# 15  
Old 01-02-2019
OBTW, I got Bootstrap tooltips working in the modal for the grid:

Image
# 16  
Old 01-02-2019
Hello Neo,

Yes, I do have 3 to 4 thoughts as follows but thinking of logic of them as of now.

1- On THANKS per post ratio, lets say 1 person is getting every 1.1 post 1 THANKS so as per that ratio badges.
2- On continuously logging days in forums one(not sure if we are keeping some information save in tables here?).
3- Monthly top thanks getting people badges(here also we need to save information somewhere, you could please let me know if we are saving this somewhere in forums?).

May be more to come Smilie


Thanks,
R. Singh
# 17  
Old 01-02-2019
  1. Thanks / Post ratio is easy, obviously since we have that data already in the grid. But this badge may not be a good idea because it could discourage people to post if they are not posting in main forums. Plus, the number of posts and thanks is already in other badges, so I'm not really keen on just badging a ratio between the two.
  2. Tracking consecutive days with forum activity may require a new DB field or a localStorage JS var in the users browser. I am guessing it is best to to this in PHP and create some new PHP logic on the DB side.
  3. Tracking monthly thanks may is easy... see next post for example query:
# 18  
Old 01-02-2019
Hey Ravinder, we can do your badge about monthly thanks based on this query!!

No need to add more backend PHP code or DB fields!

Examples:

Code:
mysql> select SUM(post_thanks_amount) from post where userid=1 and dateline > (UNIX_TIMESTAMP() -3600*24*30);
+-------------------------+
| SUM(post_thanks_amount) |
+-------------------------+
|                     128 |
+-------------------------+
1 row in set (0.01 sec)

Code:
mysql> select SUM(post_thanks_amount) from post where userid=302122727 and dateline > (UNIX_TIMESTAMP() -3600*24*30);
+-------------------------+
| SUM(post_thanks_amount) |
+-------------------------+
|                      29 |
+-------------------------+
1 row in set (0.00 sec)

You did not have this table:

Code:
mysql> describe post;
+--------------------+----------------------+------+-----+---------+----------------+
| Field              | Type                 | Null | Key | Default | Extra          |
+--------------------+----------------------+------+-----+---------+----------------+
| postid             | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| threadid           | int(10) unsigned     | NO   | MUL | 0       |                |
| username           | varchar(100)         | NO   |     |         |                |
| userid             | int(10) unsigned     | NO   | MUL | 0       |                |
| title              | varchar(250)         | NO   | MUL |         |                |
| dateline           | int(11)              | NO   | MUL | 0       |                |
| pagetext           | mediumtext           | YES  |     | NULL    |                |
| allowsmilie        | smallint(6)          | NO   |     | 0       |                |
| showsignature      | smallint(6)          | NO   |     | 0       |                |
| ipaddress          | varchar(45)          | NO   | MUL | NULL    |                |
| iconid             | smallint(5) unsigned | NO   |     | 0       |                |
| visible            | smallint(6)          | NO   | MUL | 0       |                |
| topic              | int(3)               | NO   |     | 0       |                |
| parentid           | int(10) unsigned     | NO   |     | 0       |                |
| attach             | smallint(5) unsigned | NO   |     | 0       |                |
| infraction         | smallint(5) unsigned | NO   |     | 0       |                |
| reportthreadid     | int(10) unsigned     | NO   |     | 0       |                |
| post_thanks_amount | int(10) unsigned     | NO   |     | 0       |                |
| b8rating           | float                | YES  |     | NULL    |                |
| postkeywords       | varchar(128)         | YES  |     | NULL    |                |
| description        | varchar(256)         | YES  |     | NULL    |                |
| googlebotlastvisit | int(11)              | NO   |     | 0       |                |
| bingbotlastvisit   | int(11)              | NO   |     | 0       |                |
+--------------------+----------------------+------+-----+---------+----------------+
23 rows in set (0.00 sec)

# 19  
Old 01-02-2019
Ravinder, maybe we can badge this "posts in the last month, or week) query;

What do you think?

Code:
mysql> select COUNT(postid) from post where userid=1 and dateline > (UNIX_TIMESTAMP() -3600*24*30);
+---------------+
| COUNT(postid) |
+---------------+
|           378 |
+---------------+
1 row in set (0.01 sec)

Code:
mysql> select COUNT(postid) from post where userid=302093876 and dateline > (UNIX_TIMESTAMP() -3600*24*30);
+---------------+
| COUNT(postid) |
+---------------+
|             7 |
+---------------+
1 row in set (0.01 sec)

# 20  
Old 01-02-2019
Or maybe this query, the number of threads started in the past month:

Code:
mysql> select COUNT(threadid) from thread where postuserid=1 and dateline > (UNIX_TIMESTAMP() -3600*24*30);
+-----------------+
| COUNT(threadid) |
+-----------------+
|              49 |
+-----------------+
1 row in set (0.00 sec)

# 21  
Old 01-02-2019
One more badge prototype done:... "posts in the past month"

Code:
$one_month_ago = time() - $month; 
$monthquery = "SELECT COUNT(postid) AS postcount from post where userid=".$uid ." and dateline > " .$one_month_ago; 
$monthposts = $vbulletin->db->query_first($monthquery); 
if ($monthposts['postcount'] >= 30) { 
    $color['facomment'] = 'black'; 
}  
elseif ($monthposts['postcount'] >= 15) { 
    $color['facomment'] = 'indigo'; 
} elseif ($monthposts['postcount'] >= 5) { 
    $color['facomment'] = 'blue'; 
} elseif ($monthposts['postcount'] >= 1) { 
    $color['facomment'] = 'limegreen'; 
} else { 
    $color['facomment'] = 'lightgray'; 
} 

$badgejs .= 'badge["facomment"] = "' . $color['facomment'] . '";'; 
$badgejs .= 'badge["facommentval"] = "' . number_format($monthposts['postcount']) . '";';

jQuery:

Code:
$('.fa-comment').css("color",badge["facomment"]);
$('.fa-comment').css("cursor","pointer").attr("title",  badge["facommentval"] + " Posts in the Last Month");
$('.fa-comment').closest('div').find('.fa-circle').css("color",badge["facomment"]);

Login or Register to Ask a Question

Previous Thread | Next Thread

3 More Discussions You Might Find Interesting

1. What is on Your Mind?

Badging System: UNIX.COM Bug Hunter Badge (New)

I have moved the bug badge out of reserve and into the main stream. Basically, I will assign a color level like the others, based on who has made a good actionable bug report for UNIX.COM. "Good" means screenshots, links, and even details from web dev tools our the HTML source code. So far,... (0 Replies)
Discussion started by: Neo
0 Replies

2. What is on Your Mind?

Status of Badging System - Beta 1

Dear All, Here is the current status of the badging system: The Beta 1 phase of the new badging system is close to completion. 42 prototype badges have been "allocated" 6 prototype badge slots are held in reserve The "alert you have new badges" prototype is running and is currently... (4 Replies)
Discussion started by: Neo
4 Replies

3. What is on Your Mind?

New Badging System - Badges Prototype Beta 1 (Badges Only)

Today I mapped out the new badging system using FA icons, Beta 1 in no particular order except a 6 x 8 grid: https://www.unix.com/members/1-albums215-picture991.png The prototype HTML code for this layout: <style> .fa-badge-grid { font-size: 1.5em; } .row { ... (38 Replies)
Discussion started by: Neo
38 Replies
Login or Register to Ask a Question