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. http://jade.mcli.dist.maricopa.edu/lta/author.php?a=sbell
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.
0 Comments:
Post a Comment
<< Home