Money-Code

Coding For Online Success

Including your WordPress blogs to a external website

| 2 Comments

I’ve been working on various CJ and EPN store fronts lately. Not creating new ones but working SEO and fattening them up with more reasons to visit. One of my popular ones I thought it would be cool to introduce a blog where I can promote more CJ items and promote other sites (treating it like a hub blog of sorts). I did not want to include the WordPress blog in a subdirectory, but wanted it as a sub domain (http://blog.test.com). This is located on another server (complicated circumstances), but I wanted to include recent posts within the home page of the store front.

I thought this over. I was originally just going to suck in the RSS feed from the blog, but I didn’t want the blog to impact performance in anyway. Performance can be impacted with slow delivery of the RSS (I’m already sucking in a eBay RSS feed there as well), or worse the RSS times out or has issues. I didn’t want that impression left to the potential customer.

I thought it might be cool to create a cron job to suck in the recent posts every hour via direct connection to the database on the remote server and populate a local database (the storefront). This way the ‘fetch’ process is outside the performance of the site, and the presentation of the feed would be handled by the local mysql database (very quick). Plus.. it sounded like a nerdy thing to do this morning.

To do this, I created a local wp_posts table in the local database (this is not the WordPress database). I’m only needing a few things: date, title, comments, etc.

Here is the schema:

span class=”st0″>`wp_posts` (
`ID``post_date`‘0000-00-00 00:00:00’,
`post_title``post_name`,
`post_type`‘post’,
`comment_count`‘0’`ID`

Next, I made the ‘worker’ PHP CLI script that will run on the linux server in the background. This is not executed via a browser but using the PHP executable to parse it. It basically connects to the remote (WordPress) database and queries the latest 10 records. It then truncates my local database and repopulates it with new info.

#!/usr/bin/php
‘DB_NAME’, ‘test’‘DB_USER’, ‘test’‘DB_PASSWORD’, ‘test’‘DB_HOST’, ‘mysql.test.com’‘LOCAL_DB_NAME’, ‘test’‘LOCAL_DB_USER’, ‘test’‘LOCAL_DB_PASSWORD’, ‘test’‘LOCAL_DB_HOST’, ‘localhost’"Unable to select database");
$sql            = "SELECT post_date, post_type, comment_count, post_title, post_name
                        FROM wp_8kzhbt_posts
                        WHERE post_type = ‘post’
                        ORDER BY post_date DESC
                        LIMIT 10"
"Database Error\n""\n""\n"‘post_date’‘post_type’‘comment_count’‘post_title’‘post_name’// connect to local db
        // clean the table
        // re-populate
"Unable to select database");
        $sql            = "TRUNCATE TABLE `wp_posts`""Database Error – Local Connection\n""INSERT INTO wp_posts(post_date,post_type,comment_count,post_title,post_name)
                                VALUES(‘"
‘post_date’]."’,
                                ‘"
‘post_type’]."’,
                                ‘"
‘comment_count’]."’,
                                ‘"
‘post_title’]."’,
                                ‘"
‘post_name’]."’)""Database Error – Insert Local Connection\n"

This script is added to the cron.hourly of the server is executed on a hourly basis. You could add this to crontab as well.

The next piece is on the storefront site itself. I query the local database and ask for the entries and reassembly my ‘permalink’. I could have used the guid field in the database, but it seems to convert a few of the URLs to permalinks. Not sure why, so I figured I would just create it. You may need to adjust it to fit your format.

span class=”st0″>"SELECT *
                                        FROM wp_posts
                                        ORDER BY post_date DESC""SQL Error: %d: %s\n""header"// assemble the permalink
                // http://blog.test.com/year/mo/day/post-name/
‘ ‘‘-‘‘http://blog.test.com/’.$year.‘/’.$month.‘/’.$day.‘/’"margin-bottom: 8px">
                <a href="<?=$url?>" target="_blank" rel="dofollow" style="font-weight: bold;"

Have fun with this.. hope it helps. There is literally a million ways you can do this. I would probably suck the RSS feed in directly via the worker script, then parse and insert that into the database if I was to do it again.

2 Comments

Leave a Reply

Required fields are marked *.


CommentLuv badge

This site uses Akismet to reduce spam. Learn how your comment data is processed.