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

Recommended Answers

All 13 Replies

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.

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

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.

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

please tell me the code

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.

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

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.

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 ??

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.

commented: love your help vampire link. appropriate :) +14

and how can i make this table ??

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.