Dan Knight
- 2002.04.26
Last time we filled our database with all the information we
needed for our Mac of the Day. Today we'll look at how we extract
and display that data.
Again, as the PHP neophyte, I was grateful to Brian <http://brkn.net/> and his expertise at
getting this up and running quickly. (Brian's "Buck and a Quarter"
game is done using PHP and MySQL.)
Speaking PHP
I started by creating a file called text.html. PHP code has to
be separated from regular HTML, so each section of code begins with
<?php and ends with >?. Between that is
where we do our programming.
The next step is to define where our database is. $host
is the name or IP address of your MySQL server, $username
is your user ID, and $password is your password. (I've
changed them here to protect my databases.) All of these fields are
contained between quote signs, and each line ends with a semicolon,
as below:
- $host = "sqlserver.lowendmac.com";
- $username = "MyName";
- $password = "MyPassword";
We next define our connection string $connection =
@mysql_connect($host,$username,$password); and then tell PHP
which MySQL database to use:
- mysql_select_db(lem_mod,$connection);
We haven't even looked at the data yet, only told the server
where it's located.
Since we want to change the Mac of the Day entry each day, we
next create a field that will change every day. PHP has several
ways of doing this, and we chose to use the Unix time stamp
(seconds since midnight 1/1/1970 GMT) and divide by 86,400, the
number of seconds in a day.
But we didn't want the Mac of the Day to change at midnight in
England; we wanted it to switch after midnight on the American west
coast, so we added 32,400 to Unix time - that's nine hours. This
also coincides with the time of day when traffic to our site is at
its lowest. And then we round it off to a whole number.
Here's the resulting line of code:
- $today = round((date(U) + 32400) / 86400);
$today creates a unique number and changes every day at
the same time. Now we needed to use that number to choose the Mac
of the day. Using modulo (clock) arithmetic, we defined
$mod (mac of day) by dividing $today by 89 (the
number of Macs in our list) and discarding the remainder:
- $mod = $today % 89;
Now we're finally ready to look at our database with a query
command:
- $query = mysql_fetch_array(mysql_query("select * from mod
where id = '$mod'"));
This tells the server to look up record number $mod.
Next we have to tell it what to do with the data it finds.
- echo "<LI>Mac of the Day: <A
HREF=/$query[link]>$query[model]</A>
$query[text]</LI>";
The echo (or print) command tells the server to send out the
information enclosed between quotes. The first thing we echo is
<LI>, which is the HTML code to being an item in a
bullet list. Next come the words "Mac of the Day:" followed by a
space and the HTML code (<A HREF=/ - the slash
signifies the root level of our serer) that creates the link to the
proper page.
We follow this with a greater than sign to close the link,
followed by the model name of our Mac of the Day. After this comes
</A> to close the link text and add a space
between the model name and the following text. Finally comes the
text about that Mac and the closing </LI> to
conclude the item in our bullet list. The ending quote mark and
semicolon complete the line.
And when it all runs, it produces the following line of
HTML:
- <A HREF="/1986/mac-plus/">Mac Plus</A>
(1/86-10/90). First Mac with SCSI, memory expansion. Longest model
life - over 4 years.
Which displays thus:
- Mac Plus (1/86-10/90). First
Mac with SCSI, memory expansion. Longest model life - over 4
years.
A lot of work? Maybe at first, but if it works, it will save me
maybe 5 minutes per day for the life of the site. That's worth an
hour or two of creating databases and coding.
-
- <?php
- $host = "sqlserver.lowendmac.com";
- $username = "MyName";
- $password = "MyPassword";
- $connection =
@mysql_connect($host,$username,$password);
- mysql_select_db(lem_mod,$connection);
- $today = round((date(U) + 32400) / 86400);
- $mod = $today % 89;
- $query = mysql_fetch_array(mysql_query("select * from mod
where id = '$mod'"));
- echo "<LI>Mac of the Day: <A
HREF=/$query[link]>$query[model]</A>
$query[text]</LI>";
- ?>
-
After all that, it should have worked the first time - and
it did. But then we ran into some problems, things to address in
the next column.
