More Than Cat Diaries: Publishing With Weblogs

LTA Authors Database Tables
Graphic by Alan Levine
LTA Authors Database Tables

Notes: LTA Authors LTA Authors Database Tables

  • The loc_authors table provides the information needed to display the authors information not avaialble in MovableType- first and last names and an affiliation field. By having separate name fields, we can sort results by the last name. The author_id is used as a relational bridge to the mt_authors table (to access the author email address) and link to display all entries associated with the author.
  • The lta_coauthors table provides a way to tie the ao-author's details (in MovableType this is NOT the actual author of the entry) to an entries this person should be listed as a co-author. There is one record per every LTA the person is a co-author.
  • The authors page is generated by a MovableType template (to create the correct sidebar navigation), but the main content is created by custom PHP code to query the database and provide the intended results.
  • As a feature not available on the old site, we can use the same PHP script to generate a listing of just the entries by a single author, e.g. all LTAs by Steven Bell, by passing a URL paramater of s=XXXXXXX where XXXXX is the MovableType username, e.g.

At this time, the entry of data into the 2 extra tables os done manually. The process involves:

  • Creating a new MT user account if the author is new for the site (accounts are created but never used by the authors).
  • If the author is new, their details are added to the loc_authors table.
  • The entry is first published under an editors account. Once done, the mt_entries table is edited to assign the entry author as the one listed in the first step.
  • Any co-authors are added to the lta_coauthors table

Sample Queries

  • 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";
  • Walk through the results of the query above to process each author:
    while ($author_row = mysql_fetch_array($a_result)) {
  • 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 DESC";
  • Query to find all entries where this author_id is listed as a coauthor
    $co_query = "SELECT p.entry_title, DATE_FORMAT( p.entry_created_on,  '%b %e, %Y'  )  AS pub_date
    	FROM lta_coauthor AS c
    	LEFT  JOIN mt_entry AS p ON c.mt_entry_id = p.entry_id
    	WHERE c.mt_author_id = " . $author_row['author_id'] . " 
    	ORDER  BY p.entry_created_on DESC";

The queries are simliar for cases where we extract information for a single author, except the first query looks for an author with a specific user name. See the template for the full details.


Post a Comment

<< Home