Hall of Fame

[dtiberio]

in order fo rpeopel to want to come back every day, the home page has to have new content every day, and it has to have good content every day (by likes or view count).

so we need to be able to make a home page that has new content every day (within the last 24 hours) and is sorted by popularity.

I recommend adding a column for views & counts in the last 24 hours, and using that to sort, and then resetting it every day.

otherwise, the site will either have the same thing every day, or a large mass of recent stuff that is not organized very well.

[dtiberio]

another way to sort the most popular by date is to sort by the date column but by only pulling out the year, month, and day (remove timestamp) and then order by the number of views.

something like this will give the most popular videos ordered by recency.

Code Select Expand

select * from vibe_videos order by date(date) desc, views desc;

however I can't find where in the code to make the change.

[dtiberio]

this is the code I put in under the Recent link:

       

Code Select Expand

$vq = "select ".$options.", ".DB_PREFIX."users.name as owner FROM ".DB_PREFIX."videos LEFT JOIN ".DB_PREFIX."users ON ".DB_PREFIX."videos.user_id = ".DB_PREFIX."users.id WHERE ".DB_PREFIX."videos.pub > 0 ORDER BY date(".DB_PREFIX."videos.date) DESC, ".DB_PREFIX."videos.views DESC ".this_limit();

I will test it tomorrow with fresh data to see if it works well.

[dtiberio]

I made another change to sort by the date, then views, then the hour. this will make sure that when someone hits RECENT they see first videos from the most recent day sort by most popular first, and in the case of a tie, the newest uploads come before the older uploads (sort by hour).

       

Code Select Expand

$vq = "select ".$options.", ".DB_PREFIX."users.name as owner FROM ".DB_PREFIX."videos LEFT JOIN ".DB_PREFIX."users ON ".DB_PREFIX."videos.user_id = ".DB_PREFIX."users.id WHERE ".DB_PREFIX."videos.pub > 0 ORDER BY date(".DB_PREFIX."videos.date) DESC, ".DB_PREFIX."videos.views DESC, hour(".DB_PREFIX."videos.date) DESC ".this_limit();

[dtiberio]

and here is another option which I may decide to go with:

Code Select Expand

order by views / datediff(now(), date)

it will order the results of a search by averaging the number of views per day since the video was uploaded. so new videos can move up the rankings and older popular videos should slowly drop off.

[Hersh]

What did you end up going with? This is definitely something I would like to do as well.

[dtiberio]

what I do is have the top row be the 3 most popular videos of all time.

next, I have recent videos, sorted by most popular, with a minimum of 2 views. so once it goes through the first day, it moves into the next day, and so forth. I wish it would be the top 15 or so for each day but have not gone that deep into the code and don't have enough video views yet.