1,105,263 Community Members

php code to show related posts on a blog

Member Avatar
aaloo
Junior Poster in Training
84 posts since Oct 2011
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
1
 

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.

Member Avatar
aaloo
Junior Poster in Training
84 posts since Oct 2011
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

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.

Member Avatar
aaloo
Junior Poster in Training
84 posts since Oct 2011
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
aaloo
Junior Poster in Training
84 posts since Oct 2011
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
-1
 

please tell me the code

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

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.

Member Avatar
aaloo
Junior Poster in Training
84 posts since Oct 2011
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

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.

Member Avatar
Stefano Mtangoo
Senior Poster
3,713 posts since Jun 2007
Reputation Points: 441 [?]
Q&As Helped to Solve: 394 [?]
Skill Endorsements: 2 [?]
 
0
 
Member Avatar
aaloo
Junior Poster in Training
84 posts since Oct 2011
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
1
 

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.

Member Avatar
aaloo
Junior Poster in Training
84 posts since Oct 2011
Reputation Points: 12 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

and how can i make this table ??

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
1
 

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

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

?

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: