Gossamer Forum: Products: Gossamer Links: Version 1.x: Counter update bug

Hi Alex. I've been modding some things in nph-build and I think there is a bug in the sub build_update_counters

If a resource has multiple ratings, it didn't work correctly,

I changed the line

$update_rate = $LINKDB->prepare (qq! UPDATE Links Set Votes = Votes + ?, Rating = ((Rating*(Votes-1))+?)/(Votes-1+?) WHERE ID = ?!) ...

$update_rate = $LINKDB->prepare (qq! UPDATE Links Set Votes = Votes + ?, Rating = ((Rating*(Votes-?))+?)/Votes WHERE ID = ?!) ...

and $update_rate->execute ($votes,$rating,$votes,$key) if ($votes);

to

$update_rate->execute ($votes,$votes, $rating,$key) if ($votes);

It seems to work for multiple rates. The original code works okay if there is only 1 rating, but not if there are multiple ratings before it is built.

Kevin

Hmmm... If that's true, it would explain something I bumped into a few days ago when I was playing with keeping track of the actual ratings (added a rate_log table, and inserted the ID, IP, Rating, and Timestamp into a table, for rate breakdowns).

If anyone's interested, all you need to do is set up a table:

Code:
# 
# Table structure for table 'Rate_Log' 
# 

CREATE TABLE Rate_Log ( 
   ID int(11) DEFAULT '0' NOT NULL auto_increment, 
   LinkID int(11) DEFAULT '0' NOT NULL, 
   IP varchar(15) NOT NULL, 
   Rating int(11) DEFAULT '0' NOT NULL, 
   Time timestamp(14), 
   PRIMARY KEY (ID) 
);

And insert:

Code:
$db->do ("INSERT INTO Rate_Log (LinkID, IP, Rating) VALUES ($id, '$ENV{'REMOTE_ADDR'}', $rating)");

at the end of the last last if statement just before the else clause in rate.cgi.

If you want to also store the URL, since on my site it is a good identifier, the variable is '$rec->{URL}' (quotes needed) to add to the SQL statement above, as shown below (you also need to add a URL field to the table).

Code:
$db->do ("INSERT INTO Rate_Log (LinkID, IP, Rating, URL) VALUES ($id, '$ENV{'REMOTE_ADDR'}', $rating, '$rec->{URL}')");

The reason to do this is to be able to calculate actual rates, or to get a rate history on a link. There are certainly more elegant ways, but this is what I've been doing to allow back-tracking when a good rating system is installed. At the expense of a new table, and one SQL query, it's an easy way to keep track for later use.

------------------
POSTCARDS.COM -- Everything Postcards on the Internet www.postcards.com
LinkSQL FAQ: www.postcards.com/FAQ/LinkSQL/

[This message has been edited by pugdog (edited December 15, 1999).]