Randomizer: Every 24 hours

[mysql_query]

Hello. I have a code that selects a row randomly from a MySQL database. The problem is, I need it to select a row not every time the page loads, but rather every 24 hours. This is the code that I use:

<?php 

$db = mysql_connect("localhost", "", "") or die ("could not connect to db");  

mysql_select_db("") or die ("could not select database");  

$sql="SELECT joke FROM table WHERE average=2.50 AND validation='yes' ORDER BY RAND() LIMIT 1";  

$result=mysql_query($sql, $db);  

 $row = mysql_fetch_assoc($result); 
 echo $row["joke"]; 


?> 

How would I do this?

Thanks.

[PHPdev]

My sudgestion is to create another table in the database which would store 2 things. The Id of the current Joke and the time this joke was first chosen. What you can then do is to select the with the id in this second table, and the time. Check the time to see if it is 24hrs old, if so update this table with a new random id, and time then select the new joke.

Sounds harder than it actually is...

<?php
    $db = connect to database;
    // select database;
    $sql = "select joke, chose_time from table1, table2 where table1.id = table2.joke_id;

$result=mysql_query($sql, $db);  

$row = mysql_fetch_assoc($result); 

// this part depends on how you have stored the chosen_time, I like to use Unix Timestamps, but to each his own
if ((time() - $row["chosen_time"]) > 86400) {
        $sql = //update table 2
        $sql = // Select New Joke
}
?>

[mysql_query]

I don't exactly understand what you are saying, can you please rephrase it. :o 😕

[PHPdev]

Basicly you will have two tables one for Jokes and one for refrence

Jokes

id | joke

1 | What do you call a drunk man?...

2 | Knock Knock...

Table2

joke_id | time

1 | can store this data as (2003-01-29 22:00:00) or as unix time stamp (01044082800)

The second table is telling you that the current joke to show is Joke 1, and it was first shown at 22:00 on Jan 29 2003.

What you can then do is see if Joke 1 has been showing for more than 24hrs. If so then randomly select a new joke and insert the current date time.

Basicaly I am moving the logic out of the database query and into PHP.

Let me know if this helps...

PHPdev

[mysql_query]

Hey PHPdev.

I threw together a little code, take a look:

<?php
//connect to db
$db = mysql_connect("localhost","",""); 
mysql_select_db("") or die ("could not select database");  

//select the joke id and the time to copare
$sql = "SELECT joke, time FROM jokes, jokeofday WHERE jokes.id = jokeofday.id";
$result=mysql_query($sql, $db);  

$row = mysql_fetch_assoc($result); 
//compare the time in the db against the 24 hour limit
if ((time() - $row["time"]) > 86400) {
//select a new random joke
$query = "SELECT * FROM jokes WHERE validation='yes' ORDER by RAND() LIMIT 1"; 
$resultat=mysql_query($query, $db);
$roww = mysql_fetch_assoc($result);
//make the id easir to read
$id=$roww["id"];
//put the id into the table that holds the id and time
$request = "INSERT INTO jokeofday (id) VALUES ($id)";
$insresult=mysql_query($request, $db);
//delete the old joke of the day
$finalsql= "DELETE FROM jokeofday WHERE (time() - $row["time"]) > 86400)";
$finalresult=mysql_query($finalsql, $db);
}
//whew 
?>

I don't really understand the first query $sql. Never done it beofre. Let me know if this is what you meant.

Thanks for all your help so far.

[PHPdev]

Yea that is what I had in mind. One sudgestion though.. is you dont have to do an insert and then delete you can do an update statement instead

$sql = "UPDATE jokeofday SET id = " . $id . ", time = " . time();

This query will update the id and time fields with the current id...

Now I am assuming that you put the time on the jokeofday table with the id, not in the jokes table...

Also you dont have to store a value from every execution of "mysql_query()" only when you are doing a SELECT statement or you want to check the returned value to make sure the query executed succesfully. But then again if it does not execute successfully you will see an error on the screen...

Again let me know if this solves your problem

PHPdev

[mysql_query]

Hi PHPdev. So far, you have helped a lot, thanks. But unfortunatly, I am not done yet The code (below) does not show anything after I have put in the echo command. I have tried to echo all the results (mysql_querys), but all those show me is resouse id #1, #2, #3, etc. which doesn't telll me much. I was hoping you (or someone else) might help me find the error. I have set the joke to refresh every 30 seconds (just to test). Here is the code:

<?php
//connect to db
$db = mysql_connect("localhost","",""); 
mysql_select_db("") or die ("could not select database");  

//select the joke id and the time to copare
$sql = "SELECT joke, time FROM userjokes, jokeofday WHERE userjokes.id = jokeofday.id";
$result=mysql_query($sql, $db);  

$row = mysql_fetch_assoc($result); 
//compare the time in the db against the 24 hour limit
if ((time() - $row["time"]) > 30) {
//select a new random joke
$query = "SELECT * FROM userjokes WHERE validation='yes' ORDER by RAND() LIMIT 1"; 
$resultat=mysql_query($query, $db);
$roww = mysql_fetch_assoc($result);
//make the id easir to read
$id=$roww["id"];
//put the id into the table that holds the id and time
$request = "UPDATE jokeofday SET id = " . $id . ", time = " . time(); 
$insresult=mysql_query($request, $db);
}
$show="SELECT * FROM userjokes WHERE id='$id'";
$showresult=mysql_query($show, $db);
$finalshow=mysql_fetch_assoc($showresult);
echo $finalshow["joke"];

I have manually put in the first line into the table 'jokeofday'.

Thanks for your help once again.

[PHPdev]

There were two things I notced right off that are reasons this will not work...

On this line in the IF statement
$roww = mysql_fetch_assoc($result)
needs to be
$roww = mysql_fetch_assoc($resultAT)
I changed the result to resultat

Also you only need to requery the db if you have preformed an update, so I moved the last query into the IF statement. Also notice I reused some variables mainly $query, $result, $row, so that the ECHO will ouput the joke no mater if an update has been done or not...

this is the final code I came up with... Sorry but I did leave some debugging code in there...

<?php
//connect to db
$db = mysql_connect("","",""); 
mysql_select_db("") or die ("could not select database");  

//select the joke id and the time to copare
$sql = "SELECT joke, time FROM joke, jokeofday WHERE joke.id = jokeofday.id";
$result=mysql_query($sql, $db);  

$row = mysql_fetch_assoc($result); 
//compare the time in the db against the 24 hour limit
echo(time() . " & " . $row["time"]);
if ((time() - $row["time"]) > 30) {
	echo("<br>here<br>");
	//select a new random joke
	//$query = "SELECT * FROM joke WHERE validation='yes' ORDER by RAND() LIMIT 1"; 
	$query = "SELECT * FROM joke ORDER by RAND() LIMIT 1"; 
	$resultat=mysql_query($query, $db);
	//$roww = mysql_fetch_assoc($result);
	$roww = mysql_fetch_assoc($resultat);
	//make the id easir to read
	$id=$roww["id"];
	//put the id into the table that holds the id and time
	$request = "UPDATE jokeofday SET id = " . $id . ", time = " . time(); 
	mysql_query($request, $db);
	$query = "SELECT joke FROM joke, jokeofday WHERE joke.id = jokeofday.id";
	$result=mysql_query($sql, $db);  

	$row = mysql_fetch_assoc($result);
}
echo $row["joke"];
?>

[PHPdev]

Oh... I did change the select query in the IF, I removed the where clause because of the way I setup my tables...

I setup two tables.. joke (which is your userjokes) it had two cols.. and ID int not null auto_increment primary key, and joke varchar(255) not null

and a second table jokeofday which also has two fileds id int not null auto_increment primary key and time int(14) not null

[mysql_query]

Hi PHPdev. I ried your code, but every mysql_fetch_assoc returned an error saying that it's not a valid mysql reference. I think I know why.

$sql = "SELECT joke, time FROM joke, jokeofday WHERE joke.id = jokeofday.id

There is no field called "time" in the table 'joke' (my userjokes). I think it needs that filed in both tables. Also, I am not sure about joke.id, are you sure that's how it works? I'll check the MySQL manual.

Is there any other problem that you see? Four eyes are beeter than two.

Thanks for your help.

[PHPdev]

The time is only on the joke of the day table, and it only needs to be on one table. The query is smart enough to determine this. You probably are getting this error because you need to rename the "joke" to "userjokes". This is the only thing I changed in the first query so you should not be getting an error there if you have changed this back...

Let me know how this goes...

PHPdev

[mysql_query]

Hi PHPdev.

I foolishly put in the name of the wrong database, that was my problem. However, it doesn't put up a new joke (like asked) every 30 seconds It displays the same one over and over again.

Here is the code:

<?php
//connect to db
$db = mysql_connect("localhost","",""); 
mysql_select_db("") or die ("could not select database");  

//select the joke id and the time to copare
$sql = "SELECT joke, time FROM userjokes, jokeofday WHERE userjokes.id = jokeofday.id";
$result=mysql_query($sql, $db);  

$row = mysql_fetch_assoc($result); 
//compare the time in the db against the 24 hour limit
if ((time() - $row["time"]) > 30) 
{
    echo("<br>here<br>");
    //select a new random joke
    //$query = "SELECT * FROM joke WHERE validation='yes' ORDER by RAND() LIMIT 1"; 
    $query = "SELECT * FROM userjokes ORDER by RAND() LIMIT 1"; 
    $resultat=mysql_query($query, $db);
    $roww = mysql_fetch_assoc($resultat);
    //make the id easir to read
    $id=$roww["id"];
    //put the id into the table that holds the id and time
    $request = "UPDATE jokeofday SET id = " . $id . ", time = " . time(); 
    mysql_query($request, $db);
    $query = "SELECT joke FROM userjokes, jokeofday WHERE userjokes.id = jokeofday.id";
    $result=mysql_query($sql, $db);  

    $row = mysql_fetch_assoc($result);
}
echo $row["joke"];
?>

Any help/advice/suggestions are welcome and greatly appreciated. Thanks for your help.

[PHPdev]

I noticed that when I loaded this page several times... each more than 30 sec apart several times I would get the same joke back from the RAND function...

There are two things I can sudgest to combat this.. you can add something to the WHERE clause... and id <> (current id)

Or you can try to seed the RAND() function. I know in the php manual there was some mention of using the RAND() with ORDER BY, but what you are doing now is ok.

NOTE: you probably will want to add back the WHERE validation = 'yes'.. I removed that becasue I did not notice this field when I first setup my db...

Try this...

<?php
//connect to db
$db = mysql_connect("localhost","",""); 
mysql_select_db("") or die ("could not select database");  

//select the joke id and the time to copare
$sql = "SELECT uj.id, uj.joke, jod.time FROM userjokes uj, jokeofday jod WHERE uj.id = jod.id";
$result=mysql_query($sql, $db);  

$row = mysql_fetch_assoc($result); 
//compare the time in the db against the 24 hour limit
if ((time() - $row["time"]) > 30) 
{
    echo("<br>here<br>");
    //select a new random joke
    $query = "SELECT * FROM joke WHERE validation='yes' and id <> " . $row["id"] . " ORDER by RAND() LIMIT 1"; 
    //$query = "SELECT * FROM userjokes ORDER by RAND() LIMIT 1"; 
    $resultat=mysql_query($query, $db);
    $roww = mysql_fetch_assoc($resultat);
    //make the id easir to read
    $id=$roww["id"];
    //put the id into the table that holds the id and time
    $request = "UPDATE jokeofday SET id = " . $id . ", time = " . time(); 
    mysql_query($request, $db);
    $query = "SELECT joke FROM userjokes, jokeofday WHERE userjokes.id = jokeofday.id";
    $result=mysql_query($sql, $db);  

    $row = mysql_fetch_assoc($result);
}
echo $row["joke"];
?>

I changed two of the queries hopefully this will eliminate the selecting of the same joke back to back...

PHPdev

[mysql_query]

No change 🙁 🙁 😕

I think it's just not randomizing, and something is wrong with the time() statement...

Thanks anyway.

EDIT:

When I had the script echo the time(), this is what I got:

1043959828

(It's currently 3:52PM EST. on January 30, 2003)

This is what I get when I echo $row[time]:

20030129182921

Which looks correct. This is the timestamp in my database in the jokeofday table.

I think this is the problem. Any idea how to combat it?

Thanks.

[PHPdev]

Yea you probably defined the time field as a TIMESTAMP or DATETIME rather than just an INT...

MySQL TIMESTAMP <> UNIX TIMESTAMP

Try chaning it to INT and setting the value to 0...

PHPdev

PS. As you probably know a UNIX TIMESTAMP is the number of seconds since the epoch. Which is what the PHP function time() returns... Also thus 86400 sec/day

[mysql_query]

Yes, that worked.

Thank you very much for all your help. 🙂

[PHPdev]

No Problem... Always glad when I can be of help..

PHPdev