Listing members who joined in the past week?

[rawnet]

Hello All!!

I appreciate that this may be too simple to be a hack, as it doesn't really integrate with vBulletin - not sure. Basically, I'd like to just list all my members who have joined in the past week.

This is so I can include the info in my weekly newsletter, and point visitors towards a page which just lists those who joined in the past week.

I'm just confused as to what I should use in the WHERE clause of my query - can someone enlighten me? Not sure how I select records from exactly the last week?

Cheers,

Ross

[Admin]

WHERE user.joindate > NOW()-(60*60*24*7)

[rawnet]

Hi Chen,

Thanks for such a quick response. I've tried that code - no records are being returned, so maybe I'm being a bit stupid? :


$query="SELECT user.userid, user.joindate, userfield.field11, userfield.field12, userfield.field14, userfield.field15, userfield.field24
FROM user
LEFT JOIN userfield USING (userid)
WHERE user.joindate > NOW()-(60*60*24*7)
ORDER BY user.joindate DESC";


Without the WHERE clause it returns members okay (I had a LIMIT 10 on previously)

Results can be seen at http://www.servicepals.com/latestmembers.php

Is there anything else I might be missing?

Thanks for your help,

Ross

[Admin]

Duh, my bad. Use this:
WHERE user.joindate > UNIX_TIMESTAMPT(NOW())-(60*60*24*7)

[rawnet]

Hi Chen,

Does UNIX_TIMESTAMPT work on Windows2k? Running MySQL on Win2k and PHP4.1.0

Cheers,

Ross

[rawnet]

Yes, that works a treat!!

WHERE user.joindate > UNIX_TIMESTAMP(NOW())-(60*60*24*7)

Many thanks Chen,

All the best,

Ross

[Admin]

:)

UNIX_TIMESTAMP() is because the time format vB uses is the Unix timestamp... doesn't mean it doesn't work on Win. Although I bet that if there were a Win timestamp it wouldn't work on *nix. ;)

[rawnet]

Thanks - yup, works well. I hope to move to a Linux platform shortly - hence why I moved the site from Cold Fusion to PHP. I just need to get up to speed properly on PHP first which will take a few months.

Thanks so much for your help - on a Sunday too!!

:D

Ross

[Admin]

For us Jews Sunday is just like any other day... :)