Member Avatar for 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?

  • 2 Contributors
  • 9 Replies
  • 89 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by nav33n

Dani AI

Generated

and already have the right idea: do the time filtering in SQL and avoid pulling every recent review into PHP. A few practical additions to make the server-side approach accurate and fast for a front-page "top 10" list.

Do the aggregation in the database and only return the aggregated rows you need (average + count per product), then join to the product table for titles. Require a minimum number of reviews with a HAVING clause so one highly positive review does not immediately spike a product to the top. Break ties by review count. Example pattern (adapt column names to your schema):

SELECT s.productid, p.producttitle, s.avg_rating, s.cnt
FROM (
  SELECT productid, AVG(productrating) AS avg_rating, COUNT(*) AS cnt
  FROM reviewtable
  WHERE reviewdate >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
  GROUP BY productid
  HAVING cnt >= 2
) AS s
JOIN producttable p ON p.productid = s.productid
ORDER BY s.avg_rating DESC, s.cnt DESC
LIMIT 10;

For more stable rankings use a weighted score instead of raw average. A simple Bayesian blend is:
score = (v/(v+m))avg + (m/(v+m))C
where v = product count, m = chosen minimum votes (for example 5), and C = global average. Compute that in the aggregated subquery and order by the computed score.

Indexing and scaling tips: add a composite index that matches the query pattern (for example start with reviewdate then productid) and run EXPLAIN to verify the plan. If the site gets heavy traffic, precompute the 24-hour aggregates into a small summary table via a cron job (or on-change update) and read that table for the front page — much cheaper than re-aggregating on every request.

One caution: newer MySQL modes enforce stricter GROUP BY rules. Using the aggregated-subquery pattern above avoids nonstandard GROUP BY issues and keeps results deterministic.

Recommended Answers

All 9 Replies

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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
Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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
Member Avatar for 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!)

Member Avatar for nomadhacker

much thanks.

Member Avatar for nav33n

You are welcome! :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.