I recently did a complete rewrite of the blog system, and thought I'd share my experience. This entry will cover building a simple blog system like the one found on this site, using code that is also available on this site.
http://www.charlesleifer.com/photos/blog-relational-database/
Taking a look at the table diagram, the actual entry is stored in one table, while any tags for the entry are stored in a related table, allowing an entry to have 0..n tags. The Blog class, described below, relies on these tables to store, retrieve and delete entries.
The Blog class performs 2 main functions: interacting with a single entry (CRUD operations), and searching and retrieving many entries. The Blog class provides the following methods:
A simplified version of the blog system used on this site is available for download.
The source code is broken up into 3 folders: admin/, blog/, and lib/ .
The admin/ folder contains four scripts which handle the CRUD operations:
The blog/ folder contains just one script, index.php, which loads single entries or multiple entries depending on what the user requests. User requests are parsed by the .htaccess file, which can handle requests for specific entries, dates, or tags.
The libs/ folder contains two scripts, the actual Blog class file (blog.php) and a partial script (blog_entries.php) which can be used to build a left-hand menu like the one on this site. This script shows several useful examples of how searches are performed using the Blog class.
The remainder of this post will focus on how the blog class works.
INSIDE THE BLOG CLASS There are three main operations performed on a single entry: loading, saving and deleting. Mimicking the way the database is structured, the blog class provides the following public member variables:
<?php
// used to access the currently selected entry
// called through BLOG::load()
var $id = 0;
var $title = '';
var $rewrite = ''; // url friendly version of title
var $content = '';
var $timestamp = '';
var $active = 0;
var $tags = array();
?>
These variables are populated when an entry is loaded using the blog::load() method. The load method is fairly straightforward:
<?php
// load a single entry and populate Blog object with data
// $data_array is an associative array of key/values to search, i.e.
// $_blog->load(array('id'=>4)); // load blog entry with id = 4
function load ($data_array) {
// reset entry-specific fields
$this->resetEntry();
// sanitize search array before building query
if (is_array($data_array)) {
foreach ($data_array as $key=>$value) {
$data_array[$key] = mysql_real_escape_string($value);
}
}
// match against an id
if ($data_array['id']) {
$where[] = "`id`='" .$data_array['id']. "'";
}
// match against a rewrite
if ($data_array['rewrite']) {
$where[] = "`rewrite`='" .$data_array['rewrite']. "'";
}
// build WHERE portion of query
if ($where) {
$where = " WHERE (" .implode(") AND (", $where). ")";
} else {
$where = "";
}
// execute query - limit to most recent matching entry
$sql = "SELECT * FROM `blog_entries`
" .$where. "
ORDER BY `timestamp` DESC
LIMIT 1";
$query = mysql_query ($sql) or die(mysql_error());
// output query for debugging
$this->debug ($sql);
// matching entry found => populate object with data
if (mysql_num_rows($query)) {
$row = mysql_fetch_assoc($query);
$row = $this->cleanRow($row);
$this->id = $row['id'];
$this->title = $row['title'];
$this->rewrite = $row['rewrite'];
$this->content = $row['content'];
$this->timestamp = $row['timestamp'];
$this->active = $row['active'];
// load tags associated with entry
$sql = "SELECT * FROM `blog_entries_tags`
WHERE `blog_id` = '" .$this->id. "'
ORDER BY `tag` ASC";
$query = mysql_query ($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($query)) {
$this->tags[] = stripslashes($row['tag']);
}
}
}
?>
The load() method builds a SQL query dynamically, depending on the information its given. Currently it supports the following fields: id, rewrite. The rewrite is the URL-friendly version of the title, which is generated using the following function, which replaces spaces with dashes, then strips away anything non-alphanumeric excluding a dash:
<?php
function rewriteFormat ($str) {
// clean up string and make it URL-friendly
$str = preg_replace('/[\-\s]+/', '-', $str);
$str = preg_replace('/[^A-Za-z0-9-]/i', '', $str);
return $str;
}
?>
To load using a rewrite, call $_b->load(array('rewrite'=>$rewrite));. If an id is available, simply call $_b->load(array('id'=>$id));
The save() method is just as simple. It takes a look at the blog entry parameters (id, title, content, etc), automatically generates a rewrite (in case the title may have changed or the rewrite doesn't exist yet), and then, depending on whether the "id" of the entry is set, it inserts a new record in the database or updates an existing one. Then all tags (existing or not) are purged from the database and re-inserted. Since tags are accessible through the URL (/blog/search/tag-here/), tags need to be formatted with a Rewrite as well.
The delete() method works like the save() method, using entry parameters (in this case "id") to delete an entry and its tags from the 2 database tables.
The real power of the blog system lies in its ability to search and retrieve entries matching a combination of parameters. The blog::loadEntries() method provides this functionality:
<?php
// populates blog object with entries matching a query
// $data_array is an associative array specifying the parameters to search
function loadEntries ($data_array, $limit = '', $offset = '') {
// reset object
$this->entries = array();
// sanitize array before building query
if (is_array($data_array)) {
foreach ($data_array as $key=>$value) {
$data_array[$key] = mysql_real_escape_string($value);
}
}
// match tag against url-friendly tag stored in database
if ($data_array['tag']) {
$where[] = "EXISTS( SELECT * FROM `blog_entries_tags` WHERE `rewrite`='" .$data_array['tag']. "' AND `blog_id`=t1.id )";
}
// entry published on given year
if ($data_array['year']) {
$where[] = "YEAR(t1.`timestamp`)='" .$data_array['year']. "'";
}
// use 3 letter month for clarity - find entries published on given month
if ($data_array['month']) {
$months = array('jan'=>1, 'feb'=>2, 'mar'=>3, 'apr'=>4,
'may'=>5, 'jun'=>6, 'jul'=>7, 'aug'=>8,
'sep'=>9, 'oct'=>10, 'nov'=>11, 'dec'=>12);
$where[] = "MONTH(t1.`timestamp`)='" .$months[$data_array['month']]. "'";
}
// find entries published on given day of month
if ($data_array['day']) {
$where[] = "DAY(t1.`timestamp`)='" .$data_array['day']. "'";
}
// match query against title -- not currently in use
if ($data_array['query']) {
$where[] = "t1.`title` LIKE '%" .$data_array['query']. "%'";
}
// unless "show_all" is specified, select only entries where active=1
if (!$data_array['show_all']) {
$where[] = "t1.`active`='1'";
}
// build WHERE portion of query
if ($where) {
$where = " WHERE (" .implode(") AND (", $where). ")";
} else {
$where = "";
}
// put query together
$sql = "SELECT SQL_CALC_FOUND_ROWS t1.*
FROM `blog_entries` AS t1
LEFT JOIN `blog_entries_tags` AS t2
ON t1.id = t2.blog_id
" .$where. "
GROUP BY t1.id
ORDER BY t1.timestamp DESC, t1.title ASC";
// allow for paging
if ($limit) {
$sql .= "
LIMIT " .$limit;
}
if ($offset) {
$sql .= "
OFFSET " .$offset;
}
$this->debug($sql);
// execute query
$query = mysql_query ($sql);
// results found -> populate object
if (mysql_num_rows($query)) {
$this->num_records = mysql_num_rows($query);
$this->total_records = mysql_result(mysql_query("SELECT FOUND_ROWS()"), 0);
while ($row = mysql_fetch_assoc($query)) {
$this->entries[] = stripslashes($row['id']);
}
}
}
?>
The loadEntries() function takes 3 parameters, a data array containing key/value pairs which are used to build a query. The optional 2nd and 3rd parameters are useful for building paging into your blog system, which is nice if you plan on having more than a few entries. Currently, the supported keys are:
Entry ids are stored in the entries() array. To loop through a list of entries, you would use something like the following:
<?php
// since mod rewrite parses URL into request variables, just pass
// $_REQUEST as the data array and a query will be built automatically
// use $limit and $offset (2nd and 3rd parameters) to create paging
$_b->loadEntries($_REQUEST, 0, 0);
if (count($_b->entries)) {
foreach ($_b->entries as $id) {
// load the entry
$_b->load(array('id'=>$id));
// make the date human readable
$date = date("M j, Y - g:i a", strtotime($_b->timestamp));
// display entry here
}
}
?>
Thanks for taking the time to read. Be sure to check out the online source code or the download - there are some cool features not discussed here!
Commenting has been closed, but please feel free to contact me