QUERY FOR Most Popular/Related Articles

[greenyeyes26]

Hi there

 

i have a problem building a site with news

 

table news

news_id, news_title, news_date, news_category

 

now, i want to present the 5 most popular articles based on clicks (views) of every article in a place of the main page.

 

The page that displays the articles is e.g. article.php?news_id=453

 

Also, i need to find a solution to present the 5 most related articles to the one is viewed.

 

Please ... urgent need for help ...

[GingerRobot]

Well, to be able to select the most popular, you're obviously going to have to store the number of views. So, lets say your add a field `views` to your database:

 

$sql = "SELECT * FROM `news` ORDER BY `views` DESC LIMIT 5";

 

As for your second solution, this is far more complex.

 

Im not entirely sure of the best method. However, i would assume it would envolve storing some keywords for each article. I think you'll then be needing to do a full-text search to be able to produce results based on relevance.

 

Take a look at this tutorial on full-text searches:

http://www.phpfreaks.com/tutorials/129/0.php

 

There might be a better method, however.

 

 

[dcp]

On your article.php page you'll need to update the views with each download.

 

So, before you query the db for news_id = $_GET['news_id']  (or whatever), you'll need to do something like:

 

if (isset($_GET['news_id'])) {

 

//first update the views

$query = "UPDATE news SET views = views+1 WHERE news_id = '".$_GET['news_id']."' LIMIT 1 ";

mysql_query($query) or die(mysql_error());

 

// then get the item

$query = "SELECT * from news WHERE news_id = '".$_GET['news_id']."' LIMIT 1 ";

 

. . .