Modified Date Display and Scheduled Content with PHP

Dan Knight - 2002.07.24

The more I use PHP and MySQL, the more I realize how different the combination is from using a fully integrated database program like FileMaker Pro. In FM Pro, you can define relationships between fields and have them automatically generated and universally applied. With MySQL, you have to write a program to manipulate the data.

Displaying the Date Field

But I'm learning that, and also learning equivalents to old BASIC and FileMaker commands that give me more control over presentation. For instance, MySQL delivers the results of a date field in the format yyyy-mm-dd, so a PHP script would read today's date from a MySQL database as 2002-07-24.

That's fine, but I prefer to user periods instead of hyphens (and some people prefer slashes), and I don't see any need to display the year when all the links are to new content.

In looking through our PHP books, I found the strtr function, which will replace one string with another throughout a field. In this case, I could use strtr to find every hyphen in the date field and replace it with a period. Now today's date would display as 2002.07.24.

That got me halfway there. Now I needed to find an equivalent of the rightstr command so I could extract just the five rightmost characters from the date string. The closest PHP equivalent is the substr command, which can work from either end of a string. substr(string, nn) will keep the first nn characters of a string - but if nn is a negative number, it instead retains the last nn characters.

Here's the code I used to take the MySQL date field, strip it to the last five characters, and replace any hyphen with a period:

$shortdate = $array[pubdate];
$shortdate = substr ($shortdate, -5);
$shortdate = strtr ($shortdate, '-', '.');
echo "$shortdate. ";

Which replaces echo "$array[pubdate] "; in my earlier coding.

And I think this looks much nicer.

Scheduled Content

Sometimes writers get material to me days or even weeks in advance. This gives me plenty of time to edit their articles, schedule their release date, and have them almost ready to go on their scheduled publication date. Almost.

That is, everything is ready to go except that I haven't been able to assign a record number until the day an article goes up. But by fiddling around a bit more with PHP, I can now schedule articles days or weeks in advance, assign an ID, add the article to the database, but not have it displayed until the scheduled date and time.

The first part of the process means modifying this line of code:

$latestdate = mysql_fetch_array(mysql_query("SELECT * FROM links ORDER BY timestamp DESC LIMIT 1"));

by adding one thing - ignoring timestamps later than the current time.

First we define the current time:

$rightnow = date(U);

and then we find the latest article earlier than that:

$latestdate = mysql_fetch_array(mysql_query("SELECT * FROM links WHERE timestamp <= $rightnow ORDER BY timestamp DESC LIMIT 1"));

We need to make just one more change: Don't display today's articles with timestamps later than the present:

$get_links = mysql_query("SELECT * FROM links WHERE pubdate = '$latestdate[pubdate]' and timestamp <= $rightnow ORDER BY rank DESC");

Our display script will now determine today's date and the current time, and it won't display links to articles scheduled any later than the present moment.

That's half the battle. Now I need to create a way that lets me schedule the articles by date and time, convert that to a Unix timestamp, and use that in our database instead of the current time that a record is added.

Here's the code I used to test input. Feel free to modify it as you see fit:

<?php

$unix_stamp = date(U);

$cur_year = date(Y);

$cur_month = date(m);

$cur_day = date(d);

$timezone = date(T);

$now = date(H) . ":" . date(i);

echo "<form action=$PHP_SELF method=post><input type=hidden name=process value=true><p>

Publication Date (yyyy.mm.dd): <input type=text name=pubdate size=12 maxlength=10 value=\"$cur_year-$cur_month-$cur_day\"><br>

Publication Time (no change for now): <input type=text name=pubtime size=6 maxlength=5 value=\"$now\"> $timezone<br>

<input type=submit name=submit value=submit></p></form>";

$stamp = $pubdate . " " . $pubtime . ":00";

$stamp = strtotime ($stamp);

if ($stamp >= $unix_stamp)

{$unix_stamp = $stamp ;}

?>

We begin by recording the current time as $unix_stamp, then create year, month, day, minute, hour, and timezone data from the timestamp. The year, month, and date are displayed as Publication Date, and the current time is displayed as Publication Time.

If I make no change to this information, my script uses $unix_stamp as the records time of publication, but if I change the date or time of day, it will use the new information unless I set it to earlier than the timestamp. If I do that, it will use the current time.

The strtotime function converts our yyyy-mm-dd and hh:mm data to a Unix timestamp, which is seconds since the epoch (1970.01.01).

With these modifications, I can schedule the next Lite Side article days or weeks in advance. Of course, then there's the whole different issue of Jeff Adkins coming up with something more timely between then and now....

The first article to use the new system is this one, which I wrote on Tuesday morning just before leaving for work at the camera store.