Today (Saturday) We will make some minor tuning adjustments to MySQL.

You may experience 2 up to 10 seconds "glitch time" when we restart MySQL. We expect to make these adjustments around 1AM Eastern Daylight Saving Time (EDT) US.


Notes with Ravinder on Badging System Development Part II


Login or Register to Reply

 
Thread Tools Search this Thread
# 15  
OBTW, I got Bootstrap tooltips working in the modal for the grid:

Image
# 16  
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  
  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  
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  
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  
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  
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 Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Badging System: UNIX.COM Bug Hunter Badge (New)
Neo
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,...... What is on Your Mind?
0
What is on Your Mind?
Status of Badging System - Beta 1
Neo
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...... What is on Your Mind?
4
What is on Your Mind?

Featured Tech Videos