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? :

PHP Code:

$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!!

PHP Code:

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!!



Ross

[Admin]

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