I am working on a product reviews site using php and mysql, and have a table set up for product data, as well as review data. I am trying to get a list of top rated products within the last 24 hours to show up on the front page. This is proving to be trickier than I thought. Here is my table structure:
producttable
(
productid int(10) UNSIGNED NOT NULL PRIMARY KEY
producttitle char(160) NOT NULL
productreleaseyear year(4) NOT NULL INDEX
productrating tinyint(3) unsigned NOT NULL INDEX default=1
categoryid tinyint(3) UNSIGNED NOT NULL INDEX
productnumberreviews int(10) UNSIGNED NULL INDEX
)
reviewtable
(
reviewid int(10) UNSIGNED NOT NULL PRIMARY KEY
userid int(10) UNSIGNED NOT NULL INDEX
reviewtext text NOT NULL
reviewtitle char(160) NOT NULL
productid int(10) UNSIGNED NOT NULL INDEX
productrating tinyint(3) UNSIGNED NOT NULL default=1
reviewdate datetime NOT NULL INDEX
)
Does anyone have any tips or ideas on this?