[CS-FSLUG] php sql question

[David McGlone]

On Wednesday 16 December 2009 22:02:12 Frank Bax wrote:
> 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";

Thank you Frank. Tomorrow I'm going to try this tactic and once I see it 
working, I'll most likely have a better understanding.

Also, Do you have any good recommendations for any PHP and MySQL books?

-- 
Blessings
David M.
I have been driven to my knees many times by the overwhelming conviction that 
I had nowhere else to go.