< Back to Toplists

Advanced Toplist Tasks

Construct your toplist using the toplist generator at Admin Panel -> Themes -> Toplist Generator. In a toplist, the 'filtering condition' option is the most powerful and diffcult part. This allows you to select which articles, categories, comments or members you need to show. The condition uses MySQL syntax, and this allows many options. (For those who know MySQL: the condition is placed after "WHERE" in the MySQL SELECT query.)

Filtering by Date Range

Suppose you need to show all articles from between two particular dates. There isn't any 'date' field in the database... the 'time' field is in the form of a Unix timestamp (the number of seconds since 1970). So instead you need to take advantage of MySQL's integrated functions. This filtering condition would grab everything from October 4th through 6th of 1997:

time > UNIX_TIMESTAMP('1997-10-04 00:00:00') AND time < UNIX_TIMESTAMP('1997-10-07 00:00:00')

There are many other MySQL functions listed in the MySQL manual at http://www.mysql.com/doc/en/ . Date and time functions are at http://www.mysql.com/doc/en/Date_and_time_functions.html

To get the current time, use UNIX_TIMESTAMP() with no arguments passed. Thus to get all articles from the last 10 days, your condition would be

time > (UNIX_TIMESTAMP() - 60*60*24*10)

The last portion of this is simply the number of seconds which exist in 10 days: 60 seconds per minute times 60 minutes per hour times 24 hours per day times 10 days.

Filtering by Top Level Category

Another task: Suppose you want to make a toplist of all articles in category #1 and all its subcategories. Just the category alone is easy enough by supplying a condition catid=1. Including articles from subcategories of 1 takes a more complicated condition: catid=1 OR parentids LIKE '%|1|%'. This means where the category is 1 or one of the category's parents is 1.

You can compare with any field from the database. Feel free to ask WSN support for help on making your filtering condition.