Jump to content

Algorithm to order my records


adambedford

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.