Most Popular Item

[bobbymoir]

I have a news website in PHP & MySQL. All News items are generated from the database with a php file (../shownews.php?newsid=39393). Here my problem is that i want to put MOST POPULAR NEWS ITEM and i can't figure out how to do so as its dynamically generated. Someone please help me.

Thanks in advance.
Bob

[shubhamjain1]

Monitor the visits of each item. Just as a new item is visited plus one the number of visits in the database (use cookies to count only unique visits). Then you can retrive the most popular item by using ORDER BY MySQL statement.

[bobbymoir]

Hi Shubhamjain,

Thanks for your reply and i know that it should be done that way but i don't know how the database should be.... Do i need to create a counter table or do i need to add another field on my existing table ? and please give me an example script so that i can follow it.

Thanks

[shubhamjain1]

Hi Shubhamjain,

Thanks for your reply and i know that it should be done that way but i don't know how the database should be.... Do i need to create a counter table or do i need to add another field on my existing table ? and please give me an example script so that i can follow it.

Thanks

See, Lets assume your table is like this -

NEWS_ID ............ NEWS_TITLE ............ NEWS_BODY

Now just add another field(int default value(0)) using phpmyadmin (assuming you are using mysql).

NEWS_ID ............ NEWS_TITLE ............ NEWS_BODY .............. NEWS_VIEWS

Now, if the URL of the news is like this -

http://example.com/?id=NEWS_ID

THEN excecute the following mysql query

UPDATE `table_name` SET NEWS_VIEWS=NEWS_VIEWS+1.

Now to get popular news just use the following query.

SELECT * FROM `table_name` ORDER BY NEWS_VIEWS

Hope you got that.

[bobbymoir]

Thanks Shubhamjain,

I will try with your code and reply soon.

Thanks
Bob