...

View Full Version : Designing a query, need help.



kdiggy
07-20-2006, 05:38 AM
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?

raf
07-20-2006, 07:57 AM
welcome here!


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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum