SQL in CICSWorld Pages

From CICSWiki

In the home page of the CICS World weblog site, there are several uses of SQL. The purpose of this article is to document how these are used to update the information that is on the home page as well as in the supporting pages. In fact the left navigation shown below outlined in red is created by means of an SQL statement. There is a plug-in for MovableType that allows embedding the results of an SQL statement as part of the page. This is commonly done in PHP but requires a plug-in for called simply enough SQL.

T he left navigation is driven by the following SQL:

SELECT MT_BLOG.blog_id, MT_BLOG.blog_name, MT_BLOG.blog_name REALNAME, MT_BLOG.blog_site_url, max( entry_id ) AS RANK, COUNT( * ) AS CNT
FROM cics2005_mt.mt_blog MT_BLOG
INNER JOIN cics2005_mt.mt_entry MT_ENTRY ON MT_BLOG.blog_id = MT_ENTRY.entry_blog_id
WHERE entry_status =2
GROUP BY MT_BLOG.blog_id, MT_BLOG.blog_name, MT_BLOG.blog_name, MT_BLOG.blog_site_url
ORDER BY RANK DESC

The purpose of this SQL is to poovide a link to all of the CICS weblogs listed by most to least recently updated.

The logic of the listing is to put the log names and links to the corresponding weblogs in the left columns. The results of the query are written with
separators like so:

<a href="<MTSQLColumn column="4">" alt="<MTSQLColumn column="2">"
 title="<MTSQLColumn column="2">"><MTSQLColumn column="3"></a>

One can get a little lost with all the ("} quote markes, I admit.

The listing on the left as well as the home page rebuilds at midnight. We used to rebuild it on the hour, but one day the hosting company got sore about it and claimed it was loading down their server. Most of the time it does not matter that much, but towards the end of Fall quarter, it does not seem like once per day is often enough.

On an inside page, there is a scoreboard that allows everyone to see see how the blog assignment is going. This page is a bit more dynamic and rebuilds itself each time it is accessed. There the script uses another table that gives the students that are in 602 with a blogging assignment. They are the only ones included in the assignment. Occasionally, I get someone in the list that should not be (like right now with Kirsten). Their 602 credit usually does not look so good.

This SQL is:

SELECT
   MT_BLOGGERS.blog_name AS STUDENT,
   blog_site_url,
   MT_BLOG.blog_name,
   COUNT( * ) AS COMPLETE,
   ROUND(( DAYOFYEAR( CURDATE( ) ) - DAYOFYEAR( '2005-10-1' ) ) / 7,0) AS ASSIGNED,
   ROUND(CASE
       WHEN 100 * COUNT( * ) / ( ( DAYOFYEAR( CURDATE( ) ) - DAYOFYEAR( '2006-10-1' ) ) / 7 ) >= 100 THEN 100
       ELSE 100 * COUNT( * ) / ( ( DAYOFYEAR( CURDATE( ) ) - DAYOFYEAR( '2006-10-1' ) ) / 7 )
   END,0) AS SCORE,
   blog_lastname
FROM
   cics2005_mt.mt_blog MT_BLOG, 
   cics2005_mt.mt_bloggers2007 MT_BLOGGERS, 
   cics2005_mt.mt_entry MT_ENTRY
WHERE
   MT_BLOG.blog_id = MT_BLOGGERS.blog_id
   AND MT_BLOGGERS.blog_id = MT_ENTRY.entry_blog_id
   AND ( entry_status = 2
   AND MT_BLOGGERS.blog_id NOT IN( 40, 42, 51, 52, 56 )
   AND LENGTH( entry_text ) > 150)
GROUP BY
   MT_BLOGGERS.blog_name,
   blog_site_url,
   MT_BLOG.blog_name,
   blog_lastname
ORDER BY
   7

Each year we have to tweek the SQL to get the system to calculate the % of assignment. If you look at it a bit, you will find where we drop blog entries that are really short like "This is my first blog entry."

The way we get the scoreboard.php file to work is to make it a php file. This causes the php interpreter to process it before it is served and so adds dynamic elements. For more, just google php.