How to show related articles ?

linux1880 · #1 (**permalink**) 12-20-10, 11:57

Hello friends,

I am building news site in php mysql, I want to show related articles on that article, how do i achieve it please show me sql example. Thanks

healdem · #2 (**permalink**) 12-20-10, 12:13

how do you know what article is related to another?
whatever mechanism you use place that in the where clause

eg
select my, comma, separated, column, list from my table
where articlerealtion = blah

or where articlerelation in (a, list, of, articles)

r937 · #3 (**permalink**) 12-20-10, 12:19

Quote:

Originally Posted by linux1880

please show me sql example.

Code:

CREATE TABLE articles
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, title VARCHAR(99) NOT NULL
, dateposted DATE NOT NULL
, author VARCHAR(99) NOT NULL
);
CREATE TABLE article_rel
( this_article INTEGER NOT NULL REFERENCES articles (id)
, related_article INTEGER NOT NULL REFERENCES articles (id) 
, PRIMARY KEY (this_article,related_article)
);

SELECT related.id
     , related.title
     , related.dateposted
     , related.author
  FROM article_rel
INNER
  JOIN articles AS related
    ON related.id = article_rel.related_article
 WHERE article_rel.this_article = 937

linux1880 · #4 (**permalink**) 12-20-10, 19:27

Hello friends agaain,

I am trying to write a php program for my news site where we can display "most popular articles by views" please give me an idea what database table structure I should make. Thanks

r937 · #5 (**permalink**) 12-20-10, 22:24

Quote:

Originally Posted by linux1880

please give me an idea what database table structure I should make.

Code:

CREATE TABLE articles
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, title VARCHAR(99) NOT NULL
, dateposted DATE NOT NULL
, author VARCHAR(99) NOT NULL
, views INTEGER NOT NULL DEFAULT 0
);

linux1880 · #6 (**permalink**) 12-21-10, 15:21

Thanks r937

I am bit confused about the related article article_rel table do i need to send id's in article_rel table with php or we should write database function for that ? Your suggestion will be appreceated. Thanks