WordPress and ColdFusion – The Best of Two Worlds?

Wordpress is really a force to be reckoned with as far as CMS go. With a huge community behind it, a plethora of features and frequent updates, it has become the defacto CMS to use. Other than a robust admin section, it’s also very easy to create templates for the front end as well.

Having said the above however, there might arise a need to query the WordPress database using ColdFusion. Suppose you need to create a jQuery Mobile website using ColdFusion and you need to tap into the WordPress CMS for the posts.

The easiest way of going about this, is to create a custom view within the WordPress database. The main table that houses the posts in WordPress is called “wp_posts”, and together with its related tables we can get all the data we need:

And here’s the SQL code:

SELECT
     DISTINCT 'wp_posts'.'ID'   AS 'ID',
     'wp_posts'.'post_author'   AS 'post_author',
     'wp_posts'.'post_date'     AS 'post_date',
     'wp_posts'.'post_content'  AS 'post_content',
     'wp_posts'.'post_title'    AS 'post_title',
     'wp_posts'.'post_excerpt'  AS 'post_excerpt',
     'wp_posts'.'post_status'   AS 'post_status',
     'wp_posts'.'post_parent'   AS 'post_parent',
     'wp_posts'.'post_type'     AS 'post_type',
     'wp_posts'.'menu_order'    AS 'menu_order',
     'wp_posts'.'comment_count' AS 'comment_count',
     'wp_users'.'display_name'  AS 'display_name',
     'wp_posts'.'guid'          AS 'guid',
     'wp_terms'.'name'          AS 'name',
     'wp_terms'.'slug'          AS 'slug'
FROM
(
     (
          (
               (
                    'wp_posts'
                    JOIN 'wp_term_relationships'
                    ON (( 'wp_posts'.'ID' = 'wp_term_relationships'.'object_id' ))
               )
               JOIN 'wp_term_taxonomy'
               ON (( 'wp_term_relationships'.'term_taxonomy_id' = 'wp_term_taxonomy'.'term_taxonomy_id' ))
          )
          JOIN 'wp_terms'
          ON(( 'wp_term_taxonomy'.'term_id' = 'wp_terms'.'term_id' ))
     )
     JOIN 'wp_users'
     ON(( 'wp_users'.'ID' = 'wp_posts'.'post_author' ))
)
WHERE
(
     ( 'wp_posts'.'post_type' = 'post' )
     AND ( 'wp_posts'.'post_status' = 'publish' )
     AND
     (
          ( 'wp_terms'.'slug' = 'news' )
          OR ( 'wp_terms'.'slug' = 'videos' )
          OR ( 'wp_terms'.'slug' = 'photos' )
     )
)
ORDER BY 'wp_posts'.'post_date' DESC

The query looks for all posts that have been marked as published, with categories that have slug “news”, “videos”, or “photos”. You can certainly add more arguments to the query, like the “post_date”  and so on. It all depends on your needs.

We can then save the SQL code above into a view of its own (let’s call it “view_all_posts”), and run a simple ColdFusion query against it to select the latest 10 posts:

<cfquery name="VARIABLES.qPosts">
     SELECT *
     FROM view_all_posts
     LIMIT 0, 10
</cfquery>

<cfdump var="#VARIABLES.qPosts#" />

Once we have our coldFusion query, we can simply loop through it and output the content the way we want it.