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?