PHP/Mysql to pull 5 related stories

Question

We add tags to our news posts (we use our own CMS), and I'd like to pull 5 related stories depending on what tags are used for an individual story. We have tags in the database seperated by spaces, so I use this code to get the tags into an array

$tags = explode(" ", $tags);

Now that we have the tags in an array, I want to use them to pull related stories from the database.

foreach($tags as $t) {
    $pullRelated = mysql_query("SELECT * FROM `posts` WHERE `tags` LIKE '%$t%' AND `newsID` != '$newsID' LIMIT 5");

The problem with this is that if there are 3 tags, it'll show 15 related stories (5 for each tag). What I'd like is if there are 2, 3, 4 or 5 tags, show a mix of stories from all tags, but still only show 5 stories.

Advice?

Answer 1

So you hold tags (keywords, categories) as a space delimited string in your posts table.

Well there's not much that can be said about that except you need to normalise your design which will alleviate all of your problems and make your application significantly more efficient judging on what you've shown so far.

Something along these line should suffice:

http://stackoverflow.com/questions/3534597/rewriting-mysql-select-to-reduce-time-and-writing-tmp-to-disk/3535735#3535735