How can I make a 'Today's featured article' extraction from a MySQL DataBase in PHP?

Question

How could I make a 'Today's featured article' system using PHP and MySQL?

Each day, the first time a user visits the site, I need to randomly select one article from the database, and that will be used for the whole day, for every user that comes to the site. This would happen without any intervention of the administrator, so the script would be triggered by the users visiting.

As I understand it, the first time a user visits the page on a new day, the script would randomly (or on some other basis) select an article, then flag it in the database, at the same time unflagging the article from the previous day. But what if two users are visiting the same page at the same time? This would probably mean there will be two articles flagged at the same time!

And how do you cope with time zones?

Save yourself some effort. Its not mission critical; just store it in a cookie for them. If they change PC then it loses which story, but does that matter? — Treffynnon, Jul 29 '11 at 14:57
@TomalakGeret'kal that is why this is a comment and not an answer! If it ain't that important why complicate it. — Treffynnon, Jul 29 '11 at 15:19
@TomalakGeret'kal no one said that. Read the comments again. — Treffynnon, Jul 29 '11 at 22:27

score 4 · Answer 1 · edited May 23 '17 at 12:04

What I would do is:

Run a cron job at midnight UTC. You can write offline cron jobs in PHP.
Randomly select an article id. This can take a few seconds depending on how you do it, so it's best to do it in an offline job, not during any user's PHP request.
Generate from the article a fragment of HTML, e.g. the intro for the article with a "read more..." link. Of course be careful about creating HTML containing XSS vulnerabilities.
Store the fragment of HTML in memcached under a known key.

Then when visitors visit the site, their page view just extracts the HTML fragment from memcached and outputs it directly in the page. At midnight, the HTML fragment changes atomically and the next PHP request fetches the newly saved article.

Since the cron job is the only code choosing a random article, it's not running concurrently with any other code, so no need for locking.

Not running memcached? Get used to it. Web app architecture needs some judicious, application-specific caching, and memcached is the best solution for this.

Regarding timezones, the short answer is use UTC for everything. For a lot more detail, listen to the OurSQL Podcast episode 46: It's About Time.

Thank you very much for taking the time to answer. Unfortunately, I can't apply this, since it seems that to run a cron job I would need direct access to the server, which I don't have, since I'm using a third party server. — Sean Bone, Jul 30 '11 at 08:02

score 0 · Accepted Answer · answered Jul 29 '11 at 15:10

You will need to construct 2 databases. One with the obvious news articles, the other which holds the article ID for that particular date. You can run checks in the PHP when the site is loaded to check the current date and if there exists an article. If not, the user is the first to arrive on the site and therefore find a random article and insert it into the table with the articles by day.

I am not going to write the entire script for you, but I will give you a few elements of it. As for time zones, you will use the server time and not the end-user's computer time. This is a breach of security, as they could have set a date in the future or past, over writing any data.

For users who visit the site at the "same time", this is very unlikely.

For selecting a article by date from the MySQL database you can use:

$check_sql = "SELECT * FROM todays_article
          WHERE date = '" . date("j-n-Y") . "'
          LIMIT 1";
$check_qry = mysql_query ($sql);
if (mysql_num_rows ($check_qry) == 1) {
    // Do nothing
}
else {
    $random_sql = "SELECT article_id FROM articles
               ORDER BY RAND() LIMIT 1";
    $random_qry = mysql_query ($random_sql);
    $random_row = mysql_fetch_assoc($random_qry);
    $insert_sql = "INSERT INTO todays_article
               ('article_id', 'date') VALUES
               ('" . $random_row["article_id"] . "', '" . date("j-n-Y") . "')";
    $insert_qry = mysql_query($insert_sql);
}

It's untested, but it gives you an idea of what you wanted to achieve.

Thank you very much for taking the time to answer. The option of using two tables just didn't occur to me. As for the concurrency issue, it was more of an interest rather than a real need because, as you say, the chances of two users accessing the same table at the same time are quite small. — Sean Bone, Jul 30 '11 at 08:06

score 0 · Answer 3 · answered Jul 29 '11 at 15:18

0

You can do it without having to save anything anywhere by using the date to seed a hash function that spits out an article id. I don't know that I'd recommend actually doing this, but it's fun to think about alternate solutions anyway. :)

$id = hexdec(substr(md5(date('Ymd')), 0, 8)) % $number_of_articles;

Note:

$id is not necessarily article.id, but the Nth article.
You can change how often the article changes just by changing the date format. If you want to change it hourly, just make it: date('H')

answered Jul 29 '11 at 15:18

Alex Howansky

50,515
8
78
98

Thank you for taking the time to answer. This is a very interesting solution, however it does mean that if the number of articles changes (say, an article is added), the chosen article id would change. – Sean Bone Jul 30 '11 at 07:57

How can I make a 'Today's featured article' extraction from a MySQL DataBase in PHP?

3 Answers3