Automating a WordPress Front End

Overview

One approach to integrating a blog into an existing website is to create a front end containing links into the blog. Linking to the blog itself, or a category or single post, is straightforward once one understands the HTML anchor tag. I, however, wanted something more sophisticated and automated -- incorporating titles, posting dates, authors (it's a multi-author blog) and the like from the latest posts. This entailed delving into the MySQL database housing the blog. Not finding any PHP code within Codex to do this, and not seeing how to find it within the WordPress code (v. 2.7), I wrote my own. I share it below -- code fairly easily adapted to similar applications by anyone with a little knowledge of PHP and SQL.

Example

The end product of the code below is visible at http://www.olympicpeninsulaaudubon.org. Note within the menu in the middle of the page near the top, the entry Latest Bird Sighting (some month and day) -- (Feb 25) . The month and day are entered between the parentheses dynamically with this code: <?php echo "$date"; ?>, where the variable $date is established near the bottom of the PHP function displaySighting.

Latest Bird Sighting is a link to a paragraph lower on the page, which displays the post title, author, and posting date and time -- Reported frigatebird in Port Angeles, posted by Harriet Smith on Feb 25, at 6:56 am . That display is stored in the variable $entry in the same function. Both this and the date derive from a database query using the second set of SQL in the PHP function getSQL. The title is a link to that post on the blog.

On the Home Page, the subsequent display is of the "n" (n=5 at this writing, but that could change) latest posts -- (title, number of comments, author, etc). That info derives from a search with the first set of SQL, subsequently packaged in the function displayTitles. Here are 3 examples, in which each link displays the specified post:

   * Kingfisher video (1). Posted by Harriet Smith on Feb 23, at 8:32 am.
   * Western Bluebird sighting (0). Posted by Harriet Smith on Feb 23, at 8:12 am.
   * Birdfest Banquet (1). Posted by Harriet Smith on Feb 18, at 5:30 pm.

Variable Naming Conventions

   Used in my PHP, JavaScript, Perl, and ColdFusion code.
//
   Prefix(es) precede first underline character in name.
   Each prefix is the first letter of the entity it represents.
   Multi-character prefixes are not associative. Order matters.
   Ordering is left to right (see example below).
//
   g -- global (always the first letter of prefix)
   a -- array
   h -- hash (associative array)
   (I also use l for list, and o for object, but 
     they aren't used in the code below.)
//
   Ex: $gah_db_results is a global variable, being an array
   of hashes. Typically used to store results of a database query, 
   where each hash is a database row. 
//
   No distinction between string and numeric variables. Usually
   a variable without a prefix will be a string unless the name 
   itself suggests numeric, as with $hits or $num_to_display.

PHP Code

Note the need to fill in your database info in function connect_db( ).

Code on Home Page

Near the top:

<?php
  // months, db handle, db query results
  global $ga_mos, $g_dbh, $gah_results;   
  $ga_mos = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',                
                             'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
   require_once('lib/blog_db_functions2.inc');
   blogInit();
   $sql = getSQL('sighting');
   getQueryResults($sql);
   list ($date, $entry) = displaySighting();
?>

Code to display Month day: <?php echo "$date"; ?>

Code to display title, etc. for most recent bird sighting: <?php echo "$entry. ";?>

Code to display the "n" most recent posts:

<?php
   $num_to_display = 5;
   $sql = getSQL('all');
   // results are rows of hashes with db fields as keys.
   // most recent post is last row, per ORDER BY in SQL.
   getQueryResults($sql);
   echo "<P class=mainarea>The $num_to_display ";
?>

Code in file blog_db_functions2.inc

<?php
// ======================================================
//    Functions are in alphabetical order
// ======================================================
function blogInit( ) {
   global $g_dbh;
   // accesses PEAR DB, per pg 248 in PHP Cookbook.
   require_once('DB.php');   
   connect_db( );    // hardwired for OPAS Blog
  // fetch row as hash with db fields as keys.
   $g_dbh->setFetchMode(DB_FETCHMODE_ASSOC);
   return;
}    // end of blogInit( )
//=================================================
function connect_db( ) {
   global $g_dbh;    // db handle
   // pull needed data from WordPress/wp-config.php
   $db_type = 'mysql';
   $host = 'fill in';
   $db = 'fill in';    // database name
   $user = 'fill in';    // MySQL username
   $pword = 'fill in';     // password
   $connect_param = "$db_type://$user:$pword@$host/$db";
   $g_dbh = DB::connect($connect_param);
   if (DB::iserror($g_dbh)) { die($g_dbh->getMessage()); }
   return;
}    // end of connect_db( )
//====================================================
function displaySighting( ) {
   // This code builds display for most recent sighting.
   // $gah_results contains db rows, last being most current post to blog.
   global $gah_results;
   $h_row = array_pop($gah_results);
   $title = $h_row['title'];
   $name  = $h_row['name'];
   $datetime = $h_row['datetime'];
   $post_id = $h_row['post_id'];
   // $dt = "$mon $day, at $hour:$min $ampm";
   $dt = parseDateTime($datetime);
   list ($date, $dummy) = split(',', $dt, 2);
   $entry = <<< ENTRY
   <a href="http://www.olympicpeninsulaaudubon.org/blog/?p=$post_id"
         target="_blank">$title</a>, posted by $name on $dt
ENTRY;
   $a_ray = array($date, $entry);
   return $a_ray;
}    // end of displaySighting( )
//=========================================================
function displayTitles($num_to_display) {
   // This code builds and prints (via echo) bullets.
   // It assumes caller sets up <ul> ... </ul> in HTML.
   global $gah_results;
   for ($i = 0; $i < $num_to_display; $i++) {
       $h_row = array_pop($gah_results);
       $title = $h_row['title'];
       $count = $h_row['count'];
       $name  = $h_row['name'];
       $datetime = $h_row['datetime'];
       $dt = parseDateTime($datetime);
       $post_id = $h_row['post_id'];
       $entry = <<< ENTRY2
  <li class=mainarea><a
         href="http://www.olympicpeninsulaaudubon.org/blog/?p=$post_id"
         target="_blank">$title</a> ($count). Posted by $name on $dt.
  </li>
ENTRY2;
       echo "$entry";
   }
   return;
}    // end of displayTitles( )
//==============================================================
function getQueryResults($sql)  {
   global $g_dbh, $gah_results;
  // init for array_push use; delete below
  $gah_results = array('dummy');   
   $sth = $g_dbh->query($sql);   
   if (DB::iserror($sth)) { die($sth->getMessage()); }
   //    ***********  Process search hits  *********
   $hits = 0;
   // hash keys are the "as" terms in SELECT
   // mode DB_FETCHMODE_ASSOC
   while ($h_row = $sth->fetchRow()) {    
       $hits++;
       array_push($gah_results, $h_row);
   }
   // verify successful search or quit for code fix
   if ($hits == 0)    {
       die('Error: No hits in getQueryResults() search'
           . " using this SQL:
$sql
\n");
   }
   array_shift($gah_results);    // delete init 'dummy'
   return;
}    // end of getQueryResults( )
// ==========================================================
function getSQL($mode)  {
   // NOTE that this code uses PHP's DB Pear.
   // MySQL functions like DATE_FORMAT and DAYNAME won't work.
   if ($mode == 'all') {
       $sql = <<< SQL
SELECT wp_posts.post_date AS datetime,
  wp_posts.ID as post_id,
  wp_posts.post_title AS title,
  wp_posts.comment_count AS count,
  wp_users.display_name AS name
FROM wp_posts, wp_users
WHERE wp_posts.post_status = 'publish'
   AND wp_posts.post_author = wp_users.ID
ORDER BY datetime
SQL;
   }
   else    {   
  // recent Bird Sightings, which is category 8 in wp_terms.
  // wp_posts.post_category is always 0 (unused), however.
  // Category is in wp_term_relationships table, under diff name.
  // Posts with multiple categories have one row per category
  // in that table.
       $sql = <<< SQL2
SELECT wp_posts.post_date AS datetime,
wp_posts.ID as post_id,
wp_posts.post_title AS title,
wp_users.display_name AS name
FROM wp_posts, wp_users, wp_term_relationships
WHERE wp_posts.post_status = 'publish'
   AND wp_posts.post_author = wp_users.ID
   AND wp_term_relationships.object_id = wp_posts.ID
   AND wp_term_relationships.term_taxonomy_id = 8
ORDER BY datetime
SQL2;
   }
   return $sql;
}    // end of getSQL( )
// ===================================================
function parseDateTime($datetime) {
   global $ga_mos;
   // 2009-02-23 08:12:44 is format of datetime field
   list ($yymmdd, $hhmmss) = split(' ', $datetime, 2);
   list ($yy, $mm, $dd) = split('-', $yymmdd, 3);
   // ignoring year as all entries will be recent
   $mon = $ga_mos[$mm-1];    // mm is 1-12
   $day = $dd - 0;  // force integer to drop any leading zero
   list ($hh, $mm, $ss) = split(':', $hhmmss, 3);
   // ignoring seconds as more detail than needed
   $hour = $hh;
   $ampm = $hour < 12 ? 'am'    : 'pm';
   $hour = $hour < 13 ? $hour - 0 : $hour - 12;   
   $dt = "$mon $day, at $hour:$mm $ampm";
   return $dt;
}    // end of parseDateTime( )
?>