Implementing a wicked sweet image rotator for WordPress

Posted by Matthew in Web on Wednesday, May 13, 2009 | 2 responses

3Since migrating fbcbelton.org to WordPress, I have used the awesome power of custom fields in conjunction with a simple tab script from Dynamic Drive to achieve a nice feature area on the front page. Sure, it worked well, but I had a couple of notable limitations. For a few reasons, I was limited to four tabs. Sure, I could manually add one, but on an automated basis I couldn’t have a fluid number of features.

Today, I had this wild idea that it was time to join the 21st century and add a flash image rotator to our front page. Why? Several reasons:

  1. (Probably the most realistic) because I like change
  2. because I like being able to have a fluid number of features
  3. because transitions are cool

So, I asked my friend Jason Morgan (who does not have a blog) to create a MySQL statement to pull what I needed in order to create an XML playlist on the fly. He, being the amazing guy that he is, made the most ridiculous SQL statement that I have ever seen.

SELECT wp_postmeta.meta_id, wp_postmeta.post_id, wp_postmeta.meta_key, wp_postmeta.meta_value, wp_term_relationships.term_taxonomy_id, wp_posts.post_title, wp_posts.post_name

FROM wp_term_relationships INNER JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID INNER JOIN wp_postmeta ON wp_term_relationships.object_id = wp_postmeta.post_id

WHERE (wp_term_relationships.term_taxonomy_id = 100) AND (wp_postmeta.meta_key = ‘large_photo’)

ORDER BY wp_postmeta.post_id DESC

1

There are two keys to making this work. First, you need to create a “Featured” category and then use phpMyAdmin to browse the wp_term_taxonomy table to find out what that category’s term_taxonomy_id is. (This is easiest if your category has description text entered).Ours was conveniently 100.  Every post that you want linked in your rotator will need to be in this category.  You could modify slightly to accomplish this with a tag also.

2Second, you need to add a custom field of large_photo to every post in that category. You can obviously call it whatever you like, but you’ll need to edit the MySQL statement accordingly.  In my large_photo field, I include the full URL to the image.

I then used the sample PHP at Longtail Video to get that MySQL statement to do some work for me in generating a suitable playlist for their AMAZING Image Rotator.

In any case, you can see the results here. I haven’t implemented on the main page yet as I need to reduce some of my HTTP calls before I add more bloat to the page.

Related posts

  • No related posts.


2 Responses to “Implementing a wicked sweet image rotator for WordPress”

  1. Matthew says:
    May 13, 2009 at 10:50 pm

    Just had this epiphany – you wouldn’t even really need to put the post into a category or tag at all. You could accomplish with only the custom field of “large_photo.” To remove them from the rotation, I drop them from the category. But, you could just drop the custom field.

    This SQL statement seems to work for that:

    SELECT wp_postmeta.post_id, wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.post_title, wp_posts.post_name
    FROM wp_postmeta
    INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
    WHERE (
    wp_postmeta.meta_key = ‘large_photo’
    )
    ORDER BY wp_postmeta.post_id DESC

  2. Deyson says:
    May 30, 2009 at 6:28 am

    I love what you have implemented, but I am pretty novice to Wordpress.

    So my questions are, where would I create the MySQL Statement?
    What do I do with the sample PHP from longtail?

    I use the Thesis frame work, Diythemes.com, which uses “Openhooks”, http://rickbeckman.org/thesis-openhook/

    Thank you for you time & keep up the good work.

Leave a Reply