Jump to content
Larry Ullman's Book Forums

Retrieving The Most Viewed Post?


Recommended Posts

I am trying to retrieve the most viewed post (popular) but it is not working.

 

Is this syntax right?

 

 

// Retrieve the most viewed post

 

$q = " SELECT post_id ,title COUNT(title) AS most FROM posts GROUP BY title ORDER BY most DESC LIMIT 5";

$r = @mysqli_query($dbc, $q);

 

if($r) {

while($row = mysqli_fetch_array( $r, MYSQLI_ASSOC )) {

echo ' <p><a href="article.php?id='. $row['post_id']. '"> ' . $row['most']. ' </a> </p>';

}

===========================================

Thank you

Link to comment
Share on other sites

That won't really work. What you do here is to count how many times a title occurs in the Database. That won't let you count number of view.

 

What you need to do is update the table record each time a post is queried. This query should only be used when a SINGLE POST is queried, hence, it's actually viewed. This query is run when you have a link like this:

 

- http://domain.com/?post_id=10

 

// Get value from GET array
$id = int_val($_GET['id']; // This will be post_id 10 if you use the link above

// Set queries
$q = "SELECT * FROM posts WHERE post_id = {$id} LIMIT 1";
$q .= "UPDATE posts set views=(views+1) WHERE id = {$id}"; // Notice .= here. This means it will be added to first string

// run both queries
$r = @mysqli_multi_query($dbc, $q);

 

When THAT is done, you can tweek your first query a little bit;

 

$q = "SELECT * FROM posts ORDER BY views DESC LIMIT 5";

 

That will give you 5 posts sorted by number of views

  • Upvote 1
Link to comment
Share on other sites

Thank you Antonio,

I have done the following but it is not working.

 

<?php

// Counting the views:

 

$p_id = intval($_GET['id']);// post id variable.

$q = "SELECT post_id, title FROM posts WHERE post_id ={$p_id} LIMIT 5";

$q .= "UPDATE posts SET views=(views+1) WHERE post_id = {$p_id}";

 

// run both queries

$r = @mysqli_multi_query($dbc, $q);

 

// If it ran OK :

if($r) {

while($row = mysqli_fetch_array( $r, MYSQLI_ASSOC )) {

echo ' <p>

<a href="article.php?id='. $row['post_id']. '"> ' . $row['title']. ' </a>

</p>';

}

} else {

echo 'There is no post at the moment!';

}

?>

Link to comment
Share on other sites

Sorry Antonio.

I really got stuck. There is column named views

I have done a separate queries as you can see below

<article>
<h1>Most popular post  </h1>
<?php
// Get value from GET array
$p_id = intval($_GET['id']); // This will be post_id 10 if you use the link above
// Set queries
$q = "SELECT * FROM testposts WHERE post_id = {$p_id} LIMIT 1";
$q .= "UPDATE testposts SET views=(views+1) WHERE post_id = {$p_id}"; // Notice .= here. This means it will be added to first string
// run both queries
$r = @mysqli_multi_query($dbc, $q);
$q = "SELECT post_id, title, views FROM testposts ORDER BY views DESC LIMIT 4";
$r = @mysqli_query($dbc, $q);

// If it ran OK :
if($r) {
while($row = mysqli_fetch_array( $r, MYSQLI_ASSOC )) {

   echo ' <p>
 <a href="article.php?id='. $row['post_id']. '"> Latest:  ' . $row['title'].   ' </a>
   </p>';

 }
} else {
 echo 'There is no post at the moment!';
  }
?>
</article>

 

Thank you for trying to help me.

Link to comment
Share on other sites

Counting the page views.

I am using this code:

// views counter:
$q = 'UPDATE posts SET `views` = `views`+1 WHERE post_id='.$_GET['id'];
$r = mysqli_query($dbc, $q);

to count the page views.

But I do not know if it is wright, although it is working.

What do you recommend me to do?

Thank you.

Link to comment
Share on other sites

// views counter:
$q = 'UPDATE posts SET `views` = `views`+1 WHERE post_id='.$_GET['id'];
$r = mysqli_query($dbc, $q);

This code works fine and I have noticed that when the page is viewed the order of posts in the main page (view_posts.php) changes according to the last post viewed. Although mysql query sets the order by to date_added DESC.

The problem is that the last post viewed or displayed goes first in list.

How can I fix this issue ?

Thank you.

Link to comment
Share on other sites

Please post both your scripts. The one displaying single posts, and the one displaying the list.

 

I can't wrap my head around your explanation. Only thing might be the query for getting the list could need a second ORDER BY clause, something like maybe "ORDER BY views DESC, date_added DESC". (But I don't think your problem lies there)

  • Upvote 1
Link to comment
Share on other sites

Thank you.

The query for page which lists the posts:

$q = "SELECT post_id, views ,author,  title, content, DATE_FORMAT(date_added, '%M %d, %Y') AS posted FROM posts ORDER BY date_added DESC "; 

 

Single post:

 

// views counter:

$q = 'UPDATE posts SET `views` = `views`+1 WHERE post_id='.$_GET['id'];

$r = mysqli_query($dbc, $q);

 

$q = 'SELECT views, author, title  content, DATE_FORMAT(date_added, "%M %d, %Y") AS posted FROM  posts WHERE post_id=' . $_GET['id'];
Link to comment
Share on other sites

Ghamdan, Antonio answered your last question in his very first reply, but basically, you need to order the returned results in descending order by views and limit the number of results returned to 5. In other words, end your query with:

 

ORDER BY views DESC LIMIT 5

 

That help?

Link to comment
Share on other sites

So what you're saying is that you want to first get the top 5 most-viewed posts, and once you have those, order them in chronological order (i.e., oldest first), right?

 

If that's the case, then you need to essentially use a subquery to get what you want. I think the following link is relevant to your problem:

http://stackoverflow.com/questions/9331644/mysql-order-by-date-get-10-result-back-then-order-by-id-again

Link to comment
Share on other sites

 Share

×
×
  • Create New...