Algorithm help and logic struction questions.

I'm trying to setup an automated Member of the Week section on my home page using Velocd's Member photo hack, and have it all customized the way I want, except for the algorigthm and logic structure to rotate the member each week.

If you're not familiar with the hack, it creates a table that contains the photo info for members that have uploaded their images.

I want the logic to be able to pull a new member for display each week, and not repeat until all members have been displayed once. Also, it needs to be able to pull only from members that have submitted their photo.

Any ideas? I'm slightly burnt out on coding right now, so my brain could use a little jogging on the best way to approach this with minimal queries and performance impact.

Thanks in advance!

[Gary King]

Maybe you should add a new column that holds either 0 or 1, for letting the script know if that member has already been shown once already?

Some pseudo code to help you along (the extra column that I suggest will be named chosen here):

search for a random member in table tbl_name
if member has 1 in chosen column, then skip and choose another member
if member has 0 in chosen column, then continue on with that member

For the photo part, just simply check the proper table to see if the member has uploaded a photo or not, shouldn't be that difficult (P.S. I have never used the Member photo hack).

[mbaskett]

Thanks Gary! I did some brain storming this evening, after a much needed break from coding (kinda sux sometimes when you have a large web dev team, and you're the only one that fully understands PHP, vb, MySQL, and how it ALL goes together)

Anyway, here's my VERY rough draft of the snippet of code that I *think* will work

It will require adding 2 new fields: displayweek and displayed. Display week will hold the INT of the week that member was/is displayed, and the displayed feild will be a -1,0,1 flag. 0 for not being displayed yet, 1 for being previously displayed, and -1 for being currently displayed.

Oh, and FYI, MOTW stands for Member of the week (just my little coding abbreviation

Feel free to pick it apart (aside from syntax... I know it's not right) conceptually and let me know what you all think:

PHP Code:

$currentweek = date(W);

$displayMOTW=$DB_site->query_first("SELECT * FROM memberphoto WHERE visible=1 AND displayweek=$currentweek AND displayed=-1");

// Check to see if there is an existing valid MOTW to be displayed
if ($displayMOTW) {
  Display the Member of the week
} else {
  //Set any 'old' MOTW to displayed status and pick new MOTW

  //setting all expired MOTW to displayed status
  $DB_site->query("UPDATE memberphoto SET displayed=1 WHERE displayed=-1 AND displayweek<>$currentweek");

  //count the number of remaining candidates to select from
  $candidates=$DB_site->query("SELECT COUNT(*) AS count FROM memberphoto WHERE visible=1 AND displayed=0");

  //check to make sure there are candidates available, if not, reset everyone
  if ($candidates[count] = 0) {
    $DB_site->query("UPDATE memberphoto SET displayed=0");
  }

  $newMOTW = $DB_site->query("SELECT * FROM memberphoto WHERE displayed=0 ORDER BY RAND() LIMIT 1)";
  $DB_site->query(UPDATE memberphoto SET displayed=-1, displayweek=$currentweek WHERE userid=$newMOTW['userid']");

  Display the Member of the week
} 


[Gary King]

You're missing a " for

PHP Code:

  $DB_site->query(UPDATE memberphoto SET displayed=-1, displayweek=$currentweek WHERE userid=$newMOTW['userid']"); 


And the quotes for

PHP Code:

  if ($candidates[count] = 0) { 


It should be

PHP Code:

  if ($candidates['count'] = 0) { 


Otherwise, not bad. Try it out, and reply back if you need some help.

[mbaskett]

Thanks for the feedback! Here's what I've currently got up and running, and it seems to run quite well. The only downside is that the FIRST person to view this page at the beginning of the week (Monday 12:01 AM) will have a few extra queries (6 total) in their page load, but other than that, it only adds one query on a regular basis.

PHP Code:

// Member of the week
$currentweek = date(W);

$displayMOTW = $DB_site->query_first("SELECT memberphoto.userid AS id,memberphoto.comments,memberphoto.body,memberphoto.highlights, 
                                    user.username,user.photonum,user.phototype 
                                    FROM memberphoto 
                                    LEFT JOIN user ON (user.userid=memberphoto.userid) 
                                    WHERE memberphoto.visible=1 AND memberphoto.displayweek='$currentweek' AND displayed=-1"); 

// Check to see if there is an existing valid MOTW to be displayed
if ($displayMOTW) {
  $motwid = $displayMOTW['id'];
  $motwbody = $displayMOTW['body'];
  $motwmods = $displayMOTW['highlights'];
  $motwusername = $displayMOTW['username'];
  $motwphoto = "<img border=\"0\" alt=\"$motwusername's $displayMOTW[comments]\" src=\"memberpics/photo$displayMOTW[id]_$displayMOTW[photonum].$displayMOTW[phototype]\">"; 

  eval("\$motw = \"".gettemplate('home_motw')."\";");
} else {

  //setting all expired MOTW to displayed status
  $DB_site->query_first("UPDATE memberphoto SET displayed=1 WHERE displayed=-1 AND displayweek<>$currentweek");

  //count the number of remaining candidates to select from
  $candidates=$DB_site->query("SELECT COUNT(*) AS count FROM memberphoto WHERE visible=1 AND displayed=0");

  //check to make sure there are candidates available, if not, reset everyone
  if ($candidates[count] == 0) {
    $DB_site->query("UPDATE memberphoto SET displayed=0");
  }
  //Pick new MOTW
  $newMOTW = $DB_site->query_first("SELECT memberphoto.userid AS id,memberphoto.comments,memberphoto.body,memberphoto.highlights,
                    user.username,user.photonum,user.phototype
                    FROM memberphoto 
                    LEFT JOIN user ON (user.userid=memberphoto.userid)
                    WHERE memberphoto.visible=1 AND displayed=0 ORDER BY RAND() LIMIT 1");
  $motwid = $newMOTW['id'];
  $motwbody = $newMOTW['body'];
  $motwmods = $newMOTW['highlights'];
  $motwusername = $newMOTW['username'];

  $motwphoto = "<img border=\"0\" alt=\"$motwusername's $newMOTW[comments]\" src=\"memberpics/photo$newMOTW[id]_$newMOTW[photonum].$newMOTW[phototype]\">"; 

  $DB_site->query_first("UPDATE memberphoto SET displayed=-1, displayweek='$currentweek' WHERE userid='$motwid'");

  //Display the Member of the week
  eval("\$motw = \"".gettemplate('home_motw')."\";");
}