Get random field out of MySql once a day

[flundo]

Hi!
I'm a newbie in PHP.

I'm building a site that has an article on the index page where the article changes once a day. The article is picked random out of a MySQL database.

When refering to this question, please use CODE examples.

Thanks

[Anon]

What you need is a random # generator.
What you can do is:
1. generate a # between starting primary key and ending primary key field.
2. pass the that # as the tableID to mysql...voila...you get a random article.
exmaple:

$randomNum=rand(0,2); // if u have 2 articles in ur database...

$query = "select * from tableName where tableID=".$randomNum.";

mysql_connect() // more code needed

mysql_query($query) .... etc...

this will work if ur primary key for the table is integer type....good luck
khalid

[[deleted]]

A different method:

the latest versions of MySQL let you do:

SELECT *
FROM table
ORDER BY rand()
LIMIT 1;

This will give you one random row from the table.

You can then store the ID of that row, or store the entire record on disk as a textfile which you can then include() into your main page, for better performance.

Note, this function can come up with the same record twice in a row, it doesn't check what the previous selections were.

[flundo]

Thanks guys!

But my problem is not to get the random field out of my database, but getting it once a day.
..it's an Intergerator ID field, and new articles are posted all the time.

..newbie

[Anon]

these examples all give a different article each time the page loads......

you need to have it choose the same consistently......... I'm a newbie and this is probably heaps lame...... but it will work

make a new table, call it day_article with the following fields

id (INT 5), article_id(INT 5), date

now you make todays date at the top of each page

---

$today=date(Ymh); //syntax might be wrong

$sql="SELECT * FROM day_article WHERE date='$today'";
$result=mysql_query($sql)or die('Could not get todays article sorry');

// grab the number of rows found
$num=mysql_num_rows($result);

// if the article hasn't been selected for today, go and get a random article and insert it into day_article

if($num==0)
{
$sql="SELECT article_id, article_id*0+rand() as random ORDER by random LIMIT 0,1";
$result=mysql_query($sql)or die('Could not fetch random article');
$myrow=mysql_fetch_array($result);
$article_id=$myrow["article_id"];
$sql="INSERT into day_article values('','$article_id','$today')";
$result=mysql_query($sql)or die('Could not insert article into todays list');
}

// now join the two to get one article out
$sql="SELECT day_article.article_id, articles.title, etc etc from day_article, articles WHERE date='$today' AND day_article.article_id = articles.article_id";
$result=mysql_query($sql)or die('Could not get todays article');

$myrow out your variables and you are away!

you can make a $sql to run to delete all day_articles listings older than today..... It is heaps round about way of doing it, and I'm sure there is a nifty way of getting one per day, but this will work because the first visitor of each day will set the random, and all other visitors will get that article until the date clocks over again.

if you find a better way let me know about it.

[[deleted]]

Make s simple script that runs a query like I posted, and save the output to a textfile.

Put taht script in your server's crontab and set it to run once a day.
That automatically give you a new item every day, and you don't have to query
the database every time you want to display the item.

[Anon]

OR...

you could store the date in the text file as well, then create something like:

if ($thedate == $today) then

display the article

else

generate a new text file and display the article

end if

[[deleted]]

That's a good idea;

Make your script look at the modification date of the textfile.
If the file was not created today, create a new file.
Otherwise, just use the file.
Simple and neat.

Thanks Craig!