more complex select query

[nicc9]

hello, all.

i tried to do this also looking at documentation from mysql site, but keep getting error messages:

basically i got data on a table, there is a field 'views', and a field 'published_on', related to articles.

what i need is a query that selects the articles and orders them according to views/day.

so, i'd need some operation that first figures how many days the articles have been up:

DATEDIFF(NOW(), published_on)

then, i gotta divide that by published_on, and make the query return first the articles that have had the most views a day.

any idea how i can accomplish this?

thanks a 1000!

[r937]

divide the number of days by a date? i don't understand why

[michaelwheaton]

I'd suggest adding another column into your table called 'ViewsPerDay'. Calculating this value at the database level will be much more efficient than calculating it everytime the page is viewed.

What is the error message you're getting with that DATEDIFF()?

[nicc9]

divide the number of days by a date? i don't understand why

well, it's simple.

i don't want divide days by a date, but the times the article has been read by the days it's been up, therefore finding the article's views/day.

[nicc9]

I'd suggest adding another column into your table called 'ViewsPerDay'. Calculating this value at the database level will be much more efficient than calculating it everytime the page is viewed.

What is the error message you're getting with that DATEDIFF()?

thanks, michael, i'll consider that.

would you have any idea how the query would look like, however, if i decided to do it every time the page loads?

thanks for your help.

[r937]

i don't want divide days by a date...

ah, i think i see now

instead of "then, i gotta divide that by published_on," you meant to say "then, i gotta divide views by the days difference"

select views/datediff(current_date,published_on) 
           as views_per_day
  from ...

[nicc9]

oh, yeah my bad