Needind help with difference between 2 timestamp dates

[unrelenting]

I need help with a display table I am trying to create if anyone can steer me straight.

 

In my database table I have name, dateJoined, and rating. There are about 40 rows but I just want to LIMIT 10. dateJoined is a regular INT timestamp and the rating is decimal(5,2).

 

What I want to do is create a top ten list table that will display in order from best to last.

 

The math I need to use is todays date minus dateJoined. I need this to in a number of days format so that it can then be divided into the rating column.

 

So I need the query to be like:

 

rating / (todays_date - dateJoined)

 

And I need this to display in DESC order I suppose. I will be running this in PHP.

 

I have pulled my hair out trying to get the TO_DAY deal to work.

 

Thanks in advance.

[dannyb785]

What value are you trying to sort? Joindate? Or the value that is the difference between joindate and now? Whether it's either, just do ORDER BY joindate and then DESC if you want the most recent joined or ASC of you want the ones whove been the longest.

[unrelenting]

I want it to display in order of the result of the math....

 

rating / (todays_date - dateJoined)

 

in descending order since it's a top ten list. It will show the highest number first. Some how I need a query that will do that math and spit out the list unless there is an easier way of doing it in PHP.

[dannyb785]

Ok, I'm using the column names that you provided, here's what you should do

 

SELECT *, (rating / (todays_date - dateJoined) ) AS sort_info  FROM 'table' ORDER BY sort_info DESC

 

or make DESC into ASC if that corrects the order

 

 

By doing the math with the columns and putting AS, you create a new column that is based on the function provided. And then you'd just sort by that new column.

[unrelenting]

Ok, I'm using the column names that you provided, here's what you should do

 

SELECT *, (rating / (todays_date - dateJoined) ) AS sort_info  FROM 'table' ORDER BY sort_info DESC

 

or make DESC into ASC if that corrects the order

 

 

By doing the math with the columns and putting AS, you create a new column that is based on the function provided. And then you'd just sort by that new column.

 

Thank you so much. I just couldn't seem to avoid syntax errors no matter how many different ways I tried it.

[unrelenting]

Well, it didn't return an error but it's not the result I need. I need it to divide the number of days that are derived from the (todays_date - dateJoined) part into the rating. With this query it is dividing the result of the subtraction of 2 unix timestamps into the rating. There has to be a conversion after the subtraction to find out the number of days that equals.

 

Know how to do that?  :-\

[dannyb785]

will you post the query you're using and the results you're getting?