Building A Quick-And-Dirty PHP/MySQL Publishing System - A Maniac Is Born
(Page 3 of 9 )
Now, I can implement this application using a variety of different scripting languages and technologies. I can use Perl, PHP or JSP. I can store the data in a database, in an ASCII text file, or in an XML-encoded document. My personal preference here is PHP for the scripting, and a MySQL database for the data storage - first, because I find development much faster with PHP (and I also happen to like the language a great deal), and second, because the customer's existing Web site is also built on PHP and already has an active database server.
Before reading further, you should download the source code for this case study, so that you can refer to it throughout this article (you will need a Web server capable of running PHP and a mySQL database).
Got it? Good. Now, the first thing I'm going to do is design the database tables that will hold the news information, using the information provided to me on the attributes of a press release.
Here's what I came up with:
CREATE TABLE news (
id smallint(5) unsigned NOT NULL auto_increment,
slug text NOT NULL,
content text NOT NULL,
contact varchar(255),
timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (id)
);
#
# id - unique item identifier
# slug - item title
# content - item body
# contact - contact person
# timestamp - item publication date
#
As you can see, this maps right into the information on the
previous page - I have one field for every element of a press release.
For my initial development, I'm going to populate this table with a couple of dummy records. Here goes:
INSERT INTO news (id, slug, content, contact, timestamp) VALUES ( '1',
'Megalomaniacs Inc. Is Born', 'EARTH -- A new star was born today on the
planet third closest to the sun. Megalomaniacs Inc., a venture of
WeWantItAll Corp., today threw open its doors for business in the ritzy
Jefferson Square business district.
Created with the sole goal of colonizing every single planet in the known
Universe (and beyond), Megalomaniacs Inc. hopes to quickly acquire a
monopoly over the vast tracts of uncharted real estate in space. Speaking
at a press conference, Megalomaniacs Inc. CEO warned reporters that
Megalomaniacs Inc. would "take everything it could, and then
some". ', 'Peter Paul (peter@megalo.mania)', '2001-02-01 17:29:25');
INSERT INTO news (id, slug, content, contact, timestamp) VALUES ( '2',
'Megalomaniacs Inc. Expands To Mars', 'MARS -- As part of its business
strategy of "expand and swallow", Megalomaniacs Inc. today
announced that it had successfully sent a team of corporate raiders to
Mars, in an effort to persuade the inhabitants of that planet to surrender
their planet for colonization.
Megalomaniacs Inc. COO today said that the move was a "friendly
overture", but that a failure to comply with the company's
colonization plans would result in a "swift and sure eviction of
those little green guys". ', 'Tim Jr. (tim@megalo.mania)',
'2001-07-11 12:13:48');
Right. Let's get on with some code.{mospagebreak title=The
Simple Stuff} You'll remember, from the requirements discussion a couple pages back, that this development effort can broadly be split into two parts. One part consists of the scripts that retrieve the list of newest items from the database and display this list to the user; the other consists of administration scripts that allow editors to manage this list, enter new information, and edit or delete existing information.
Since the first part is simpler, I'm going to get that out of the way first. The scripts involved in this are:
"list.php" - the script which retrieves a list of the five newest entries in the database;
"story.php" - the script which displays the full text for the selected story.
These scripts are stored within the "user" directory in the source code archive.
Here's my first script, which displays the list of five most recent press releases from the database.
<?
// list.php - display list of five most recent press releases
?>
<!-- page header - snip -->
<ul>
<?
// includes
include("../conf.php");
include("../functions.php");
// open database connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to
connect!");
// select database
mysql_select_db($db) or die ("Unable to select database!");
// generate and execute query
$query = "SELECT id, slug, timestamp FROM news ORDER BY timestamp DESC
LIMIT 0, 5";
$result = mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
// if records present
if (mysql_num_rows($result) > 0)
{
// iterate through resultset
// print article titles
while($row = mysql_fetch_object($result))
{
?>
<li><font size="-1"><b><a href="story.php?id=<? echo $row->id;
?>"><?
echo $row->slug; ?></a></b></font>
<br>
<font size="-2"><? echo formatDate($row->timestamp); ?></font>
<p>
<?
}
}
// if no records present
// display message
else
{
?>
<font size="-1">No press releases currently available</font>
<?
}
// close database connection
mysql_close($connection);
?>
</ul>
<!-- page footer - snip -->
There's no magic here at all. This script simply connects to
the database, retrieves a set of records, and formats them for display in a Web browser. Let's look at it in detail.{mospagebreak title=An Anatomical Exploration} The first step is, obviously, to open a connection to the database through which queries can be transmitted - this is accomplished via PHP's mysql_connect() function:
<?
// open database connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to
connect!");
?>
In case you're wondering, the host name, user name and
password are all variables sourced from the configuration file "conf.php". This file has been include() at the top of the script, and it looks like this:
<?
// conf.php - configuration parameters
// database configuration
$host = "localhost";
$user = "mm_273";
$pass = "secret";
$db = "mm_db6388";
// default contact person
$def_contact = "Johnny Doe (jd@megalo.mania)";
?>
Extracting this configuration information into a separate
file makes it easier to update the application in case the database username or password changes. It's far easier to update a single file than it is to update multiple scripts, each with the values hard-wired into it.
Next, a database needs to be selected, via the mysql_select_db() function:
<?
// select database
mysql_select_db($db) or die ("Unable to select database!");
?>
Again, the database name comes from the configuration file
"conf.php".
With the connection open, the next step is to execute an SQL query, via the mysql_query() function:
<?
// generate and execute query
$query = "SELECT id, slug, timestamp FROM news ORDER BY timestamp DESC
LIMIT 0, 5";
$result = mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
?>
Note the addition of the DESC clause in the SELECT statement
above - this orders the items in the order of most recent first - and the additional LIMIT clause, which restricts the resultset to five items only.
Once a resultset has been returned, I've used the mysql_fetch_object() function, in combination with a "while" loop, to iterate through it and format the fields in each row for display.
<?
// if records present
if (mysql_num_rows($result) > 0)
{
// iterate through resultset
// print article titles
while($row = mysql_fetch_object($result))
{
?>
<li><font size="-1"><b><a href="story.php?id=<? echo $row->id;
?>"><?
echo $row->slug; ?></a></b></font>
<br>
<font size="-2"><? echo formatDate($row->timestamp); ?></font>
<p>
<?
}
}
// if no records present
// display message
else
{
?>
<font size="-1">No press releases currently available</font>
<?
}
?>
In case you're wondering, the mysql_fetch_object() function
is a nifty little thing I only discovered recently. It converts each row into a PHP object, and represents each column within that row as a property of that object; field values can then be accessed in standard object notation. Therefore, in order to access the column named "slug" of each row, I would simply use the code
$row->slug
The formatDate() function you see in the code above is a
function I wrote to turn a MySQL timestamp into a human-friendly date string. Here's the function definition:
<?
// format MySQL DATETIME value into a more readable string
function formatDate($val)
{
$arr = explode("-", $val);
return date("d M Y", mktime(0,0,0, $arr[1], $arr[2], $arr[0]));
}
?>
Note the addition of several lines of code that tell the
script what to do if no records are returned by the query. If I didn't have this, and the database turned out to be empty (which might happen the very first time the application was installed for use), the generated page would be completely empty - not a very nice thing to show to your users, especially on a potentially high-traffic page. So I've worked around this with some code that displays a neat little message if no records are found in the database.
<?
// if records present
if (mysql_num_rows($result) > 0)
{
// iterate through resultset
// print article titles
}
// if no records present
// display message
else
{
?>
<font size="-1">No press releases currently available</font>
<?
}
?>
It's important, as a developer, to think through all possible
situations and write code that handles each one intelligently. The possibility of an empty database doesn't even occur to many novice developers - and this can lead to embarrassing situations if you're demonstrating the application to your boss...or worse, the customer.
Finally, with all the records processed and displayed, the mysql_close() function terminates the database connection.
<?
// close database connection
mysql_close($connection);
?>
Here's what it all looks like:
Next: Bedtime Stories >>
More PHP Articles
More By icarus, (c) Melonfire
/ 42 
