getting list of top rated products

[nomadhacker]

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?

[nav33n]

select * from reviewtable where reviewdate >= date_sub(NOW(), INTERVAL 24 HOUR) order by productrating desc

I think this should work. I am selecting only those records where reviewdate is greater than (present-time - 24 hours).
P.S. I haven't tested it. But I think it will work.

[nomadhacker]

This actually helps make things a lot easier in terms of the time encoding, and will cut out some php programming for that, thank you. I had been planning on calculating the date in php, this should speed things up a lot.

I guess I'm looking at loading all those into an array and just calculating the top 10 movies in php. I had been hoping someone would have an idea on a way to trim off some processing time, by performing some of that calculation in the query, but now that I look at the problem again, there doesn't appear to be any way to do that.

[nav33n]

This would list all the records in the reviewtable in descending order(only those within 24 hrs). But if you want only 10 rows, then you can use limit.

select * from reviewtable where reviewdate >= date_sub(NOW(), INTERVAL 24 HOUR) order by productrating desc limit 0,10

[nomadhacker]

Yes, but the limit's not the problem. There will probably be multiple reviews for the same product in a given day. And I want to rank based upon the average of all the reviews in that 24 hour timeframe. So I'm going to have to first grab all the ratings and corresponding productids from the reviewtable within 24 hours, then I have to calculate the averages, then display the top 10 only. It looks like it's going to be a lot more php code than I had hoped for from a loading time standpoint, because I can get the productid and ratings from the reviewtable, and match them up with the producttitle for each row, but the averaging and final ranking looks like it'll all have to take place in php. That's just an awful lot of data that I was hoping to not have to grab just to throw away.

[nav33n]

Example,

select AVG(productrating) as average  from reviewtable group by productid order by average desc

Calculates the average of all the products and puts the product with more average on top. I am sure you can make use of that.

[nomadhacker]

Now that's reaaally helpful. If I select other data along with that, will it avoid duplicate data? So say I modify this a little and put:

SELECT AVG(r.productrating) AS average, r.productid AS productid, p.producttitle AS title 
FROM reviewtable AS r 
LEFT JOIN producttable AS p ON r.productid = p.productid 
GROUP BY productid ORDER BY average desc LIMIT 10

[nav33n]

umm.. I don't think this would be any problem.. Check that out.. Joins is not my forte! But by looking at the query, I think it will work! (not sure though!)

[nomadhacker]

much thanks.

[nav33n]

You are welcome! :)