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.
