A while ago I built a football podcasting site for a client using WordPress. If you’ve ever used it or developed with it then you’ll know how powerful and fun to use WordPress can be. We needed to have a mobile version of the site so fans could listen to the podcasts on their phones after the match. There are a few mobile plugins that can be used to mobile-ise your WordPress site, now these are great but if someone’s using an Iphone and the podcast contains a flash player then it’s not going to work thanks to Apples decision to not support Flash.
So, I had to create a seperate mobile site which had a basic link to the podcasts Mp3. There were a few things that the site had to do. Firstly if someone was using a mobile phone to access the site it had to redirect them to the mobile version. It then had to show the podcasting categories in a clear usable fashion. it then had to get the posts from the WordPress database by category name, meaning that I had to use MySQL to access the WordPress Database tables.
Quite a few folks thought it wasn’t possible to select posts by category name outside the WordPress installation…. Well as it turns out pretty much most things are possible with PHP and MySQL.. I Found this post about selecting the posts by category id, which was almost exactly what I was looking for, so I tweaked it a bit and ta-da, have a look at the SELECT statement below.
1 2 3 4 5 6 7 8 9 |
SELECT DISTINCT * FROM wp_posts a, wp_term_relationships b, wp_term_taxonomy c, wp_terms d WHERE b.object_id = a.id AND a.post_status = 'publish' And a.post_type = 'post' AND c.term_taxonomy_id = b.term_taxonomy_id AND c.taxonomy = 'category' AND c.term_id = d.term_id AND d.name='my category name' ORDER BY a.post_date DESC |
