What I wanted to create was a dynamic Table of Contents page where I could have a listing of all of my published articles, organized by category, and sorted such that categories with the most posts appear first, and the posts under each category are listed from most recent at the top to oldest at the bottom. I couldn’t find a WordPress plugin for this, so I thought I would try to create it myself using Python.<\/p>\n
I was able to accomplish this using a Python script that makes use of the MySQLdb module to query the MySQL database that WordPress uses to store all of my articles. Then on the Table of Contents page, I use an iframe to automatically load and launch the Python script that creates the ToC.<\/p>\n
Every time a new article is published and I load the Table of Contents page, the query is run and a new, possibly reordered Table of Contents is generated.<\/p>\n
First I had to figure out how to get Python to connect to my MySQL database to run queries. This was made more difficult due to the fact that my web host only has Python 2.5 and supports a very limited number of modules. One of the modules it does support though is MySQL-python. Here is an example of the code used in Python to connect using this module:<\/p>\n
#!\/usr\/bin\/python\r\nimport MySQLdb\r\n\r\ndef connect():\r\n # Connect to the database\r\n connection = MySQLdb.connect('mywebhost.com', 'my_username', 'my_password', 'my_databasename')\r\n try:\r\n cursor = connection.cursor()\r\n cursor.execute(sql)\r\n result = cursor.fetchall()\r\n except:\r\n print(\"Error: unable to fetch data\")\r\n connection.close()\r\n print_output(result)\r\n\r\nif __name__ == '__main__':\r\n connect()<\/pre>\nHere is the SQL code I needed in order to get the post titles, post dates, post name slugs (used for creating the static links), and categories. With those four things, I had everything I needed to create the ToC:<\/p>\n
SELECT p.post_title as 'Post Title',\r\n t.name as 'Category',\r\n p.post_date as \"Post Date\",\r\n p.post_name as \"Post Name\"\r\nFROM wp_posts p,\r\n wp_terms t,\r\n wp_term_relationships tr,\r\n wp_term_taxonomy tx\r\nWHERE p.post_type = 'post'\r\nAND p.post_status = 'publish'\r\nAND tx.taxonomy = 'category'\r\nAND p.ID = tr.object_id\r\nAND tr.term_taxonomy_id = t.term_id\r\nAND tx.term_id = t.term_id;\r\n<\/pre>\nThe last parts I needed to complete this project were:<\/p>\n
1. Creating the logic in the Python script to build the links and organize the ToC based on categories
\n2. The Python code needed to point to all of the CSS resources that my site uses
\n3. The iframe in the Table of Contents page in order to load the Python script<\/p>\n
The final result can be seen here:<\/p>\n