Reviews Section

[Mark]

I thought I would cull this string of posts from the early thread as it has gotten lost in the shuffle, I think.

Not sure if this has been addressed but it appears that products that haven't been reviewed are treated as having no stars and thus bring down the rating of a publisher.

Rats.. Hmm.. Well... I uh.. As soon as I figure out a correction to the query I'll recalculate it.

Shouldn't you just average the existing reviews without regard for how many products there are?

Select Sum(stars) as total_starts, Count(*) as total_reviews, publisher_id from reviews; (or join the reviews and pubs table if the pub_id is not available on the review table)

if (count > 0) stars = total_stars/total_reviews else stars = 0;

The number of products shouldn't enter into it.

The correct query, I believe, will be

SELECT AVG(score) AS score FROM product WHERE publisherid = $publisherid AND score > 0

Haven't had time to test it yet - been taking a break.

But that averages the product scores which are already an average. So if a publisher have two products: one with 100 reviews and an average of 3 and one with 1 review of 5, the publisher will have a score of 4 by that querry. That's why my querry was off of the (assumed) review table instead of the product table.

Anyway, there it is...

 

[Crothian]

Thanks Mark. I think other items like having people be able to add products and getting the reviews sortible by reviewer and getting the Mods our ability to more easily function over there have taken a slight priority.

 

[Michael Morris]

That among other things. Anyway, I have gotten the member review lists up and running. These are part of a user's profile now.

Anyway, I will have a 2 week break to work on this - I intend to have everything trounced within that time.

 

[Michael Morris]

Mark, I've begun work on the admin section of the reviews site. I should have this information fixed shortly.

 

[Mark]

Mark, I've begun work on the admin section of the reviews site. I should have this information fixed shortly.

Thanks, Michael!

 

[Mark]

Took a break from working with the reviews system to make some changes Russ requested for the downloads section.

OK. Here's the problem and why I think you need to prioritize the "Publisher's Ratings in the Reviews Section" problem higher, at least insofar as addressing it in some way even if you cannot fix it. It is currently giving out FALSE information and potentially damaging the reputations of publishers who work very hard to produce quality products. I understand you are quite busy and perhaps have a different idea of what is important. That's fine. I don't expect you to hold the same opinions as I do. However, if you can't fix them, the least you can do is to take them offline until they are correct.

 

[Michael Morris]

It will be fixed today - specifically tonight.

 

[Michael Morris]

Ok it's fixed. For those who know PHP and SQL - here's the algorythm.

	$getproducts = $DB_site->query("
	SELECT productid as id from product
	");
	
	while ($product = $DB_site->fetch_array($getproducts))
	{
		$getproductavg = $DB_site->query_first("
		SELECT AVG(score) AS score 
		FROM review
		LEFT JOIN post as post ON(post.postid = review.reviewid)
		WHERE post.threadid = $product[id]
		AND score > 0
		");
	
		$productscore = round($getproductavg['score']);
	
		// Insert it.	
		$DB_site->query("
		UPDATE product
		SET weightscore = $productscore
		WHERE productid = $product[id]
		");
	
		// Find Publisher ID
		$getpublisherid = $DB_site->query_first("
		SELECT publisherid
		FROM product
		WHERE productid = $product[id]
		");
	
		$publisherid = $getpublisherid['publisherid'];
		
		// Find the Publisher's average product rating.
		
		$getpublisheravg = $DB_site->query_first("
		SELECT AVG(weightscore) AS score
		FROM product
		WHERE publisherid = $publisherid
		");
		
		$publisherscore = round($getpublisheravg['score']);
	
		// Insert it.	
		$DB_site->query("
		UPDATE publisher
		SET avgscore = $publisherscore
		WHERE publisherid = $publisherid
		");
	}

 

[Mark]

Ok it's fixed.

No, it isn't. Somehow, and I won't pretend to follow the code since it is not my area, you have the total of review scores divided total products, even when some products aren't reviewed. It's my understanding that a Publisher's rating should simply be total reviews averaged, or perhaps averaged for each product, and then averaged again and divided by the number of products reviewed (though this latter method can be decieving if one product has only one review and it is reviewed to an extreme, while other products received multiple reviews that are then more indicative of an actual average).

If one has a three star review, a four star review and a five star review, they have a Publisher rating of four stars, regardless of how many products they have in total and regardless if some haven't been reviewed at all. Currently, it would seem, if a publisher had the above suggested reviews, perhaps all for a single product, but also had eleven additional products (for a total of twelve products) they'd have a publisher rating of one star (three stars plus four stars plus five stars equal twelve stars divided by twelve products equal a rating of one star)

 

[Michael Morris]

Were's this happening Mark? It'd be useful to watch that specific publisher while I tinker with it to make sure it corrects out.