Logo         Community
  Trog
Services
The Company
Community
Columns
Your Account
Contact Us
 
 
Building A Quick-And-Dirty PHP/MySQL Publishing System
Looking to quickly add a dynamic news page to your corporate or personal Web site? This article shows you how.

| 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, (int)$arr[1], (int)$arr[2], (int)$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:

Output image


How to do Everything with PHP & MySQL
How to do Everything with PHP & MySQL, the best-selling book by Melonfire, explains how to take full advantage of PHP's built-in support for MySQL and link the results of database queries to Web pages. You'll get full details on PHP programming and MySQL database development, and then you'll learn to use these two cutting-edge technologies together. Easy-to-follow sample applications include a PHP online shopping cart, a MySQL order tracking system, and a PHP/MySQL news publishing system.

Read more, or grab your copy now!


previous page more like this  print this article  next page
 
Search...
 
In trog...
Logging With PHP
Output Buffering With PHP
Date/Time Processing With PHP
Creating Web Calendars With The PEAR Calendar Class
Caching Web Sites With PEAR Cache
more...
 
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
more...
 
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
more...
 
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
more...
 
In cut!...
American Chai
The Core
Possession
more...
 
Find out how you can use this article on your own Web site!


Copyright © 1998-2018 Melonfire. All rights reserved
Terms and Conditions | Feedback