Algorithm to order my records

[adambedford]

I'm writing a script where I need to order my results based on two things: the number of hits they receive, and the date the record was created.

 

Ideally, I want the most popular records at the top (or do I, I'm not sure of this yet!) but I also need newly created records to have a chance of receiving hits (ie. if they are new and don't have any hits, I don't want them to be at the bottom from the get-go and not have a chance of being seen.)

 

My hits are recorded in a separate table and the 'date created' field is in the main table.

 

Does anyone have any idea as to how I can organise these records to that the new ones get a fair chance of getting clicked. I also want to avoid the situation where the most popular ones remain at the top and don't allow the others a chance to get clicked. I don't want these records to spiral out of control with regards to hits.

 

I'm relatively new to PHP and I don't really have a clue where to start with this.

 

Any help would be greatly appreciated.

[jl5501]

if you have your hits in a table called hits, and your main table is called main and your hits table has a field called main_id then you can do this

 

select * from main,hits where hits.main_id=main.id order by hits.hitcounter desc,main date_created desc

 

Hope that helps

[adambedford]

Thanks for your reply!

 

How would that order the records? Would it just put everything descending? I was hoping to find a way to mix in the newly created records with the existing, popular ones to give them a chance of getting hits.

 

Many thanks!

[jl5501]

The query I gave you will give you your records in the order of descending hit count, and within that, descending date

 

There are all sorts of options you can play with with those and other fields in your data that you want to sort on

[Psycho]

Well, this really isn't a PHP or even MySQL question. Moving topic.

 

But, on the face of it, one solution would be to sort by the hits per day: TOTAL_HITS/DAYS_OLD. So, if you have a link that is one day old and it has 1 hit, it will be sorted above a link with 300 hits that was a year old. Trying to develope the "right" method of displaying the most relevant results is more of an art form than a skill.

 

Personally, I would do something like hits per day AND also reserve some room at the top for 3-5 random NEW links - last day or two. Otherwise, new links will still not make the list as they will always start with 0.

[adambedford]

Thank you both for your replies

 

mjdamato, how would I go about implementing your suggestion? My main table, 'links' stored the date the record was created, 'Date_created' and my 'hits' table stores the hits in the field 'hits'. The two are associated by the URL, field 'URL'.

[Psycho]

You will need to calculate the days between the date_created and today and then divide the hits by that number. BUT - you have to be careful of not doing a division by zero.

 

I'm too lazy to try all o this out, but here is a quick run down of what I was able to google for in 30 seconds. Although, I have no idea why you would store the URL creation record and the hits in two separate tables.

 

This is what I would try:

SELECT links_table.URL, hits_table.hits as hits,
       DATEDIFF(CURDATE(), links_table.date_created) AS days,
       IF(days<1, hits/days, 0) as hits_per_day
FROM links_table
JOIN hits_table ON links_table.URL = hits_table.URL

 

Although, I'm not 100% sure you can use a calculated value (i.e. days) in a subsequent equation. If not, then you would have to write out the entire formula:

 

SELECT links_table.URL, hits_table.hits as hits,
       IF(DATEDIFF(CURDATE(), links_table.date_created)<1,
          hits/DATEDIFF(CURDATE(), links_table.date_created),
          0) as hits_per_day
FROM links_table
JOIN hits_table ON links_table.URL = hits_table.URL

 

Again, none of this is tested.