SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot nicc9's Avatar
    Join Date
    Jan 2005
    Location
    New Orleans, LA
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question more complex select query

    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:

    Code:
    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    divide the number of days by a date? i don't understand why
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict michaelwheaton's Avatar
    Join Date
    Jan 2001
    Location
    Alberta, Canada
    Posts
    338
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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()?

  4. #4
    SitePoint Zealot nicc9's Avatar
    Join Date
    Jan 2005
    Location
    New Orleans, LA
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  5. #5
    SitePoint Zealot nicc9's Avatar
    Join Date
    Jan 2005
    Location
    New Orleans, LA
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by michaelwheaton View Post
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by nicc9 View Post
    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"
    Code:
    select views/datediff(current_date,published_on) 
               as views_per_day
      from ...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot nicc9's Avatar
    Join Date
    Jan 2005
    Location
    New Orleans, LA
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, yeah my bad


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •