Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jul 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Designing a query, need help.

    I need a little help setting up the logic behind a query. Here is the situation:

    I have 2 tables. Product table. Flag table (flagID, productID, date_flagged). Users can flag products. *date_flagged is a INTEGER unix timestamp.

    I would like to be able to setup a query where I can pull up products that have been flagged the most in the past 24 hours.

    At first I figured I would COUNT() the flags and GROUP BY productID, but this of course will only give me the most flagged all time, and because of the grouping by productID will not allow me to filter it by date.

    I really don't see what sort of query can do this for me. Any ideas?

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    welcome here!

    Quote Originally Posted by kdiggy
    At first I figured I would COUNT() the flags and GROUP BY productID, but this of course will only give me the most flagged all time, and because of the grouping by productID will not allow me to filter it by date.
    why not? you can just add a where clause like

    SELECT COUNT(*) as numrec, productID FROM table WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <=date_flagged GROUP BY productID ORDER BY numrec LIMIT 20

    what do you mean by "date_flagged is a INTEGER unix timestamp". is it a column of type timestamp or is it the unix-time that you stored in a colun of type integer?
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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