How to create/find a Joomla/Community Builder 'Latest Users' module? Playing with dates in MySQL/Joomla...

Question

A few years ago when Joomla first sprung up and I moved over from Mambo I remember finding a great little component called Community Builder (CB) that extends the user-based interface behind Joomla. Over the last couple of years both Joomla and Community Builder have grown and flourished.

I find myself remembering a small module I found when first toying with CB that displayed a couple of useful details about users that I want to be able to extract from my current project. These details included:

• Total users registered
• Total users registered per day
• Total users registered per week
• Total users registered per month
• Latest user registered
• Total visitors to the site

My question is mainly about how to recreate something very similar. I've been trying to construct an SQL statement that can manipulate the user table results and compare them to a set criteria eg. find all user's with a 'registerDate' variable within the last week.

The problem that has me lost is how to manipulate and compare a date string that resembles '2008-08-05 07:41:40'

Any help on either issue would be much appreciated.

Answer 1

The problem that has me lost is how to manipulate and compare a date string that resembles '2008-08-05 07:41:40'

You can have MySQL turn this into a UNIX timestamp, or you can run it through strtotime.

MySQL:

SELECT UNIX_TIMESTAMP(datetime_field) FROM table

strtotime:

$timestamp = strtotime('2008-08-05 07:41:40');