php code to show related posts on a blog

[aaloo]

i m making a blog using my own code .. i m not using wordpress or any sort of thing .. i m doing it manually. i want to show related posts section which would contain at least 4 links of another posts which are related to that particular post. can someone write that code for me ?? i know that i have to create a column in my database containing tags ..but i have no idea about the code.. thanx in advance

[pritaeas]

I guess you have a blog table. You'll need another table for keywords and another one to link blogs to keywords. If you have those, I can give you a query that will match blog entries based on keywords.

[aaloo]

i have a table called entries in which i have columns for posts and keywords ..i dont have separate tables.still i want ur code ..i will edit it according to my database design

[pritaeas]

That is not that easy, since the query is based on those 3 tables for a reason. I will post my tables, and php code for the query when I get home.

[aaloo]

okk ..i will make it ..i desperately need this code to increase traffic on my blog

[aaloo]

please tell me the code

[pritaeas]

Basically, I use these tables (simplified from what I have):

Articles
  id             bigint 20  unsigned  pk  autoinc
  date_modified  datetime
  name           varchar
  title          varchar
  content        text

Tags
  id    bigint 20  unsigned  pk  autoinc
  name  varchar 


Articles_Tags
  article_id  bigint 20  unsigned
  tag_id      bigint 20  unsigned

Any tag you use should be in the 'Tags' table. The third table links articles and tags together by their id's.

If I retrieve an article, I use this query:

$query = "
  SELECT a.*, GROUP_CONCAT(t.name SEPARATOR ' ') AS tags 
  FROM articles a 
  LEFT JOIN articles_tags at ON a.id = at.article_id 
  LEFT JOIN tags t ON at.tag_id = t.id 
  WHERE a.name = '{$article_name}' 
  GROUP BY a.id";

Column 'tags' will contain the articles tags separated by a space. To use it in my next query I convert it like this:

$tags = "'" . implode("','", explode(' ', $row['tags'])) . "'";

Now it will be a comma separated string list, ready for use in my query. The following query will get the 5 best matching articles based on it's tags.

$query = "
  SELECT a.name AS name, a.title AS title, COUNT(*) AS relevance 
  FROM articles_tags, tags t, articles a 
  WHERE article_id <> {$article_id} 
  AND tag_id = t.id 
  AND article_id = a.id 
  AND t.name IN ($tags) 
  GROUP BY article_id 
  ORDER BY relevance, a.date_modified DESC LIMIT 5";

Hope this helps.

[aaloo]

thanks for your code :) .But please explain the code line by line . i am not getting it

[pritaeas]

What don't you get. There are only two lines of code, one to get the article, and one to get the related ones. The rest is up to you. If something is unclear, please be more specific.

[Stefano Mtangoo]

not sure if it will help
http://stackoverflow.com/search?q=related+articles

[aaloo]

What don't you get. There are only two lines of code, one to get the article, and one to get the related ones. The rest is up to you. If something is unclear, please be more specific.

heyyy sorry for the late reply ,actually i lost the link of this thread .
i want to know how will u make the third table Article_tags , manually or using some sql query ??

[pritaeas]

I have my own cms, so when I add an article with tags, this table gets filled with a query per tag. The table itself is added manually of course, just like the other two.

[aaloo]

and how can i make this table ??

[diafol]

Didn't P give the table structures and associated queries here:

http://www.daniweb.com/web-development/php/threads/387961/1684151#post1684151

?