Wordpress - Related Posts Script
At my job, I currently have three Wordpress sites under my watchful eye. On one, I was tasked with creating a related posts feature. Now, there are certainly enough plug-ins that will do just that, but none of them worked how I wanted to or were generally more complex than necessary. So, I whipped up this little script:
Code: php
- function relatedPosts($id) {
- /* Build a query to get a list of all posts that share similar tags */
- $s = microtime(true);
- $q = "SELECT count(*) AS total, p.ID, p.post_title, p.post_date FROM wp_posts p INNER JOIN wp_term_relationships t ON p.ID=t.object_id AND t.term_taxonomy_id in (SELECT s.term_taxonomy_id FROM wp_term_relationships s WHERE s.object_id=$id) AND t.object_id != $id AND p.post_status='publish' GROUP BY p.id ORDER BY total DESC, p.post_date DESC LIMIT 5";
- /* Format the output */
- $out = "";
- $result = @mysql_query($q);
- if ($result && @mysql_num_rows($result) > 0) {
- $out = "<h3 class=\"related\">Related Posts</h3><ul class=\"related\">";
- while ($row = mysql_fetch_object($result)) {
- $perma = get_permalink($row->ID);
- $out .= '<li><a href="'.$perma.'">'.$row->post_title.'</a> - '.date("F j, Y", strtotime($row->post_date)).'</li>';
- }
- $out .= "</ul>";
- }
- echo($out);
- }
- ?>
Just drop that in the wp-content/yourtheme/functions.php file, where yourtheme is the directory of your active theme. Then add the following in your wp-content/yourtheme/single.php where you want the related posts to appear:
Code: php
- relatedPosts(get_the_ID());
- ?>
You should now have a list of posts with similar tags. Styling is simple: one h3 and one ul, both with the class "related".
Enjoy!
Comments
Great. How do I have to change the query if I want this + only results from the same category?
ReplyI've got this now ...
SELECT count(*) AS total, p.ID,p.post_title, p.post_date, p.post_category
FROM wp_posts p
INNER JOIN wp_term_relationships r ON p.ID = r.object_id
INNER JOIN wp_term_taxonomy t ON r.term_taxonomy_id = t.term_taxonomy_id
AND r.term_taxonomy_id IN ( SELECT r.term_taxonomy_id FROM wp_term_relationships r WHERE r.object_id = $id)
AND ( SELECT t.term_id
FROM wp_term_relationships r, wp_term_taxonomy t
WHERE r.term_taxonomy_id = t.term_taxonomy_id
AND t.taxonomy = 'category' AND r.object_id = $id )
IN ( SELECT r.term_taxonomy_id
FROM wp_term_relationships r
WHERE r.object_id = p.ID )
AND r.object_id != $id
AND p.post_status = 'publish'
GROUP BY p.id
ORDER BY total DESC, p.post_date DESC
LIMIT 5
ReplyUppss.... the second Join is useless.
ReplyHeh, you freaked me out there. Was checking through the comment logs and thought somebody was attempting an SQL injection attack >_>
Glad you got that query working for ya ^_^
Reply:)
Reply