Featured Album Pics from active members only

[richy96]

Hi

I use a module to show featured album pics on several of my site pages

These are displayed at random

However what I would like to do is show random pics but only from member that have been logged in during the last 30 days.

That's not the same thing as pics that have been added in the last thirty days - the pics could be over a year old, as long as the members has been logged in recently they should be candidates for display

The current SQL is

$orderby = "rand(NOW())";

$lpictures = $db->query_read("
  SELECT albumpicture.*, album.*, user.username, user.usergroupid,
  IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid 
  FROM " . TABLE_PREFIX . "albumpicture AS albumpicture
  LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.albumid = albumpicture.albumid)
  LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = album.userid)
  WHERE album.state = 'public' 
  ORDER BY $orderby DESC LIMIT 0, $tot  
  ");

How do I get it to only select randomly but only from members who have recently logged in? Sorry but my SQL abilities are not very good and I can't quite get my head around this.

Thanks
Rich

[kh99]

I think you could do something like this:

$orderby = "rand(NOW())";
$cutoff = TIMENOW - (30 * 86400);  // 86400 = seconds in one day

$lpictures = $db->query_read("
  SELECT albumpicture.*, album.*, user.username, user.usergroupid,
  IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid 
  FROM " . TABLE_PREFIX . "albumpicture AS albumpicture
  LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.albumid = albumpicture.albumid)
  LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = album.userid)
  WHERE album.state = 'public' AND user.lastvisit > $cutoff  
  ORDER BY $orderby DESC LIMIT 0, $tot  
  ");

[richy96]

Working great! Thanks