Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Posts
    39

    Unanswered: How to show related articles ?

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by linux1880 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2010
    Posts
    39

    How to write a most popular articles by views

    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by linux1880 View Post
    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
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2010
    Posts
    39
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by linux1880 View Post
    do i need to send id's in article_rel table with php
    yes, sending with php will be okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2010
    Posts
    39
    Thank you,

    just wondering how do i do it to show popular post this week , this month and this year.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    related articles... check

    most popular by views... check

    popular this week, this month, this year... time for you to try it yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •