[CS-FSLUG] php sql question

[Frank Bax]

David McGlone wrote:
> Hey all,
> 
> I've been working on a simple PHP MySQL script that will change a picture on a 
> certain day every month as to try and understand PHP and MySQL better. In this 
> particular code I'm trying to learn about the rand() function and I'm 
> wondering if it can be controlled. For instance I'm trying to use the random 
> function to grab a random row from a database and display that row for a set 
> amount of time. I have some code (pasted below) that I have been experimenting 
> with, but for the life of me, I can't think of a way to get the row to stick 
> when the browser is refreshed.
> 
> My train of thought on how to accomplish this is there (at least I think it 
> is) LOL, but I just can't get it out into my code. I created steps to try and 
> sort out my thought process step by step like this:
> 
> 1. Grab a random row from the DB on a given day lets say the 16th.
> 2. display that row from this day (16th) up until the 16th of next month.
> 
> simple enough, but I'm stumped. When I use my code, with the rand() function 
> in the SQL query, the row always changes on a refresh of the browser. If I try 
> and use the exit; function then the rest of the page is cut short and none of 
> the php code is executed below the exit; function.
> 
> Here's the code I've been trying to work it out with so far:
> 
> $today = date('j');
> 
> if ($today == 16) {
> $query = "SELECT * FROM monthlyPooch ORDER BY RAND() LIMIT 1";
> $result = mysql_query($query);
> $row = mysql_fetch_row($result);
> $name = "<p>$row[1]:  $row[4] - $row[5] - $row[6]</p>";
> 
> echo "$name";
> echo "<img src=images/$row[3]";
>    
> }
> 
> There's got to be a way I can stop the rand() function from executing more 
> than once.
> 
> Would anyone by any chance have an idea?
> 



RAND() will always be called on every browser refresh.  The way to avoid 
this would be to create a new table with these fields.
     savedate int
     picture text
     caption text

Use this table to remember when rand() was last called.  Only call 
rand() if today's real date is different that the date value in this new 
table.  I hope you can figure out my pseudo-code.


** SELECT the only row from this new table
$lastdate = $row[?]
$filename = $row[?]
$caption  = $row[?]
if ($today <> savedate) {
   $query = "SELECT * FROM monthlyPooch ORDER BY RAND() LIMIT 1";
   $result = mysql_query($query);
   $row = mysql_fetch_row($result);
   $caption = "<p>$row[1]:  $row[4] - $row[5] - $row[6]</p>"
   $filename = $row[3];

   ** UPDATE savedate = now()
   ** UPDATE picture = $filename
   ** UPDATE caption = $caption
}

   echo $caption;
   echo "<img src=images/$filename";