Ghamdan Posted October 19, 2012 Share Posted October 19, 2012 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 More sharing options...
Antonio Conte Posted October 19, 2012 Share Posted October 19, 2012 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 1 Link to comment Share on other sites More sharing options...
Ghamdan Posted October 19, 2012 Author Share Posted October 19, 2012 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 More sharing options...
Antonio Conte Posted October 19, 2012 Share Posted October 19, 2012 Read my post again. You need to add a column called 'views' to you post table. You then need separate queries for the front page and the single post page. Link to comment Share on other sites More sharing options...
Ghamdan Posted October 19, 2012 Author Share Posted October 19, 2012 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 More sharing options...
Larry Posted October 19, 2012 Share Posted October 19, 2012 I'm generally against using mysqli_multi_query(), but if you are using it, you need to put a semicolon between the queries. Link to comment Share on other sites More sharing options...
Ghamdan Posted October 26, 2012 Author Share Posted October 26, 2012 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 More sharing options...
Edward Posted October 26, 2012 Share Posted October 26, 2012 Just put an SQL update on your script page at the top which adds 1 to the views each time the script is viewed. Link to comment Share on other sites More sharing options...
Ghamdan Posted October 26, 2012 Author Share Posted October 26, 2012 Thank you, Edward. I have added it at the beginning of the script before the main script to view the post. 1 Link to comment Share on other sites More sharing options...
Edward Posted October 26, 2012 Share Posted October 26, 2012 Thats exactly as i done for mine and it worked like a chime, good luck with your work. Link to comment Share on other sites More sharing options...
Ghamdan Posted October 29, 2012 Author Share Posted October 29, 2012 // 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 More sharing options...
Antonio Conte Posted October 29, 2012 Share Posted October 29, 2012 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) 1 Link to comment Share on other sites More sharing options...
Ghamdan Posted October 30, 2012 Author Share Posted October 30, 2012 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 More sharing options...
HartleySan Posted October 30, 2012 Share Posted October 30, 2012 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 More sharing options...
Ghamdan Posted October 30, 2012 Author Share Posted October 30, 2012 What I am trying to do is ORDER BY dated_added not views. But the problem is the list is order by active views, means the last post viewed goes first in list which I do not want to. Thank you . Link to comment Share on other sites More sharing options...
HartleySan Posted October 30, 2012 Share Posted October 30, 2012 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 More sharing options...
Recommended Posts