vote/rate add-on query

if i wanted to add a voting/rating process to something, how would i go about doing it?

i was thinking just set up an additional table

confession id | rate | voter (bbusername)

and then just join that to the other table via left joins?

[sabret00the]

lol, since this morning i've dummified, so how would i go about this after i set up the tables lol

[Dean C]

Moved to the correct forum

[assassingod]

You would do something like:

PHP Code:

$DB_site->query("UPDATE table SET rate = '$oldrate + 1', voter = '$bbuserinfo[username]'"); 

And use another query to get $oldrate

That's the way I did it for my vB3 point system (I think :dumbass: )

[sabret00the]

but wouldn't that just add or whatever to the rate was already there instead of working out an average rate?

[assassingod]

Originally Posted by sabret00the

but wouldn't that just add or whatever to the rate was already there instead of working out an average rate?

You didn't say anything about working out the average rate, but to work out the average you would use

PHP Code:

$avgrate = $DB_site->query("SELECT avg(rate) FROM table"); 

[sabret00the]

thanks i'll try implementing this code

[sabret00the]

ok in setting up the table would this be right?
[sql]CREATE TABLE confession_rate (
rateid MEDIUM INT UNSIGNED NOT NULL AUTO_INCREMENT,
confessionid BIGINT(20) ( SIGNED NOT NULL,
userid INT(10) SIGNED NOT NULL,
timestamp INT(11) NOT NULL,
rate SMALLINT(2) SIGNED NOT NULL,
PRIMARY KEY (rateid)
);[/sql]?
confessionid is set via php $confessionid
user_id is set by $bbuserinfo[userid]
timestamp is set when a confession is voted for
rate is what you're rating the confession (1-10)
and i set the primary key to by the confession id but that wouldn't work so i've decided i need another row for rateid.

[sabret00the]

figured out i don't need the left joins but would be great if you can tell me if i set that SIGNED and UNSIGNED stuff right.

[assassingod]

This would be a better table structure:
[SQL]
CREATE TABLE confession_rate (
rateid TINYINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
confessionid TINYINT(5) NOT NULL,
userid TINYINT(5) NOT NULL,
time TIMESTAMP(14) NOT NULL,
rate SMALLINT(5) NOT NULL,
PRIMARY KEY (rateid)
);
[/SQL]