[CS-FSLUG] php sql question

[David McGlone]

On Thursday 17 December 2009 18:22:26 David McGlone wrote:
> 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?
> 

Hi Steven & Frank. Thanks for your suggestions, I spent a while working with 
Franks examples, and learned a lot. After that, I tried Steven's approach, and 
it also worked like a charm.

I always knew PHP couldn't do stuff in real time like java, but with this 
experimentation I finally seen it first hand and the reality of it sunk in.

Now that I realize this limitation in PHP it's got me wondering if it could be 
changed.

I went as far as trying to write a conditional statement that would run a 
different header that included a refresh of the page at a certain time with the 
time() function, but I realized that nothing was going to happen unless the 
page with the random function was also executed, so this idea got thrown out 
the window.

I probably could have put another header on the function page, and had it auto 
refresh at certain intervals, but then php would spit out an error that the 
headers had already been sent. I could have worked around this by manually 
adding headers to each page, but that would be a real pain if this were a real 
site with hundreds of pages.

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