As reported a few times before, I have been prying time here and there to work on a prototype web site, that replaces a current, hand edited Low Threshold Applications web site with a dynamic one authored in MovableType. I am just polishing off a new add-on hack that uses PHP and mySQL to dome nifty magic.
We are making progress on moving the old content over, but one of the last features to tackle (I think) was a dynamic generated equivalent of the LTA Contributors List where it lists the authors names, emails, organizations, and a list and links to the content they had authored.
After exploring the available tags in MovableType, plugins, I was still not finding a simple way to do this. Yes, MT can sift through entries sorted by authors, but you have to know all the author username in advance, so every new author would mean a rewrite of the template. Not dynamic enough for me.
I found a solution very close to what I was after from Scriptygoddess (a heavenly MT resource), MT hacks: Master Author Archive, MySQL version and with not too much leverage, was able to customize my own. Basically, I use PHP to pull info on all the authors on this particular blog, then for each author, echo their contact details. Under each, he trudge the database again, and for each author, pull just the entry titles they have posted to build a list and links.
However doing this the way we wanted meant needing extra information on the author, as MT’s author table just lists their login name and email, and I needed their full name, and their organization/affiliation. So my approach here was to create my own local author table in the MT database, and tie it to the MT one via the MT generated author_id (I am likely losing all of the non programmers here, bye ;-).
My author table, named loc_author has the structure:
the mt_author_id is the same as the unique id in the MT author table. I was lazy and just manually entered the data via phpMyAdmin, making sure that each LTA had an account created for it, that the entries were assigned to the appropriate author. I did set up all that content in an Excel spreadsheet, and could ahve just as well written a short script to load it from a tab delimited text file, but just decided to plug in the data as is.
The first thing I do for my own database projects is to create a general include file with a function I can call from anywhere to establish a database connection. I called it something like mt_connect.php and store in a general location in the main directory structure:
<?php
/*
Use to connect to MT database
by: alan
updated: 041220
*/
// set global constants
define('MYSQL_HOST','localhost');
define('MYSQL_USER','XXXXXX');
define('MYSQL_PASS','xxxxx');
// contact
define('CHIEF','Alan Levine <alan.levine@domail.maricopa.edu>');
function db_connect($db,$script=null) {
/* *********************************************************************
dbConnect($db)
connects to the main mysql server, selects database given in params
($db), and returns the reference link. if it fails it generates an
email notice to the CHIEF
********************************************************************* */
$ln = @mysql_connect(MYSQL_HOST,MYSQL_USER,MYSQL_PASS);
if ( $ln && mysql_select_db($db,$ln) ) {
return ($ln);
}
else {
// if connected to server then database error
$failure = ($ln) ? 'database: '.$db : 'MySQL Server';
$msg = MYSQL_HOST . " Can't connect to the $failure.\n\nMySQL reports:\n".mysql_error();
if (!is_null($script)) $msg .= "\n\nError occurred in: $script";
mail(CHIEF,'MySQL error',$msg,'From: '.CHIEF."\nX-Mailer: PHP/".phpversion());
return false;
}
}
?>
So I can use this by simply including this file in any PHP script and then just calling this function with the database name. One connect script can be used all over your site.
And then here is the author.php code. There are a few things particular to our site, especially on the links to individual archives. All LTAS have a tile that is in the form:
LTA #XX — Some Big Cool Title
and the associated archive link is:
/lta/archives/ltaXX.php
so we use some pattern matching to pull out the numerical part of the title string and build the corret link.
So here is the whole author code:
<?php
// establish database connection
include ($_SERVER['DOCUMENT_ROOT'] . '/SOMEPATH/mt_connect.php');
$db = db_connect('movabletype', '/lta/author.php');
// Database id for this blog, fill in your own
$blog_id = XX;
/* Get all authors by querying MT authors tables and
our local authors table (which includes first name,
last name, and affiliation)
*/
$a_query = "SELECT a.fname, a.lname, a.affil, mt.author_email, mt.author_id
FROM mt_author AS mt
LEFT JOIN loc_author AS a ON mt.author_id = a.mt_author_id
LEFT JOIN mt_permission AS p ON p.permission_author_id = mt.author_id
WHERE p.permission_blog_id = $blog_id
AND mt.author_id = a.mt_author_id
ORDER BY a.lname";
$a_result = mysql_query($a_query) or die (mysql_error());
// cycle through the found authors for this blog
while ($author_row = mysql_fetch_array($a_result)) {
// Print info for each author
echo '<li><strong><a href="mailto:' . $author_row['author_email'] . '">' .
$author_row['fname'] . ' ' . $author_row['lname'] . '</a></strong> (' .
$author_row['affil'] . ')';
// For each author select the entries that they have published
$e_query = "SELECT entry_title, DATE_FORMAT(entry_created_on, '%b %e, %Y')
AS pub_date
FROM mt_entry
WHERE entry_blog_id = $blog_id
AND entry_author_id = " .
$author_row['author_id'] .
" ORDER by entry_created_on";
$e_result = mysql_query($e_query) or die (mysql_error());
// number of contributions for this author
$count = mysql_num_rows($e_result);
// keep our grammar correct
$plural = ($count>1) ? 's' : '';
// number of entries authored
echo " has authored <strong>$count</strong> LTA$plural: <ul class="nospread">";
while ($entry_row = mysql_fetch_array($e_result)) {
// to construct a URL to link to, we need the LTA# XX -- from the title
// since our archives are in the form /archives/ltaXX.php
preg_match("/(\d+)\b/", $entry_row['entry_title'], $matches);
// output entry titles
echo '<li><a href="/lta/archives/lta' . $matches[0] .
'.php">' . $entry_row['entry_title'] . '</a> (' .
$entry_row['pub_date'] . ")</li>\n";
}
echo "</ul>\n</li>\n";
}
?>
And the whole thing can be seen in action at:
http://jade.mcli.dist.maricopa.edu/lta/author.php
It looks like a rather simple web page, but it is going in real time into the database to generate this content. I thought that it might be a load on the database, and could just as easy set this up to be a script that generates the content as a static include file, that is sucked into the template. For now, it runs as quick as loading plain old HTML, so I’ll leave it.
