Single user allowed to rate multiple times?

[bhenkel]

Hello,

Is there a way to allow a single user to make multiple rating on single photo, and avoid the "You have already rated this photo" message?  Thanks in advance for your help.

[Nibbler]

Remove the check from ratepic.php

Code Select Expand


// Check if user already rated this picture
$user_md5_id = USER_ID ? md5(USER_ID) : $USER['ID'];
$sql = "SELECT * " . "FROM {$CONFIG['TABLE_VOTES']} " . "WHERE pic_id = '$pic' AND user_md5_id = '$user_md5_id'";
$result = cpg_db_query($sql);
if (mysql_num_rows($result)) cpg_die(ERROR, $lang_rate_pic_php['already_rated'], __FILE__, __LINE__);


[bhenkel]

Thanks for your reply, Nibbler.  I tried removing that code and received a "There was an error processing a database query" after trying to rate pic a second time.  Do you know anything about this?  Thanks for your help.

[Nibbler]

Sorry, put this line back in

Code Select Expand

$user_md5_id = USER_ID ? md5(USER_ID) : $USER['ID'];

[bhenkel]

Hmmm, still did it.  Maybe something with my database.  Thanks Nibbler!

[Spot_69]

Hi there,

   I would like to make a change similar to this, and am having a similar problem.

   Goal: allow users to re-rate pictures at their whim, without incrementing the rating counter, just changing the rating stored in the database for that user

   Questions:
       - how do I take out the check for whether or not the user has already rated, without causing a database access error?
       - how do I make it so that subsequent ratings do not increment the ratings counter, and/or do not get stored as additional ratings?

   I'll try and dig into this further myself, but my SQL skills are very limited, my initial attempts at messing about with ratepic.php were not all that successful, and any help would be greatly appreciated.

   Thanks in advance!

[zac]

Another vote for this.. I tried that too Nibbler.. same problem.

[Spot_69]

For what it's worth - I tried to dig into this further as promised, and failed miserably.

Any further help would be greatly appreciated!

[Nibbler]

Code Select Expand

$sql = "INSERT INTO {$CONFIG['TABLE_VOTES']} " . "VALUES ('$pic', '$user_md5_id', '$curr_time')";

Try making that a REPLACE instead.

[zac]

closer.. now it counts and calculates the votes... however it still sends me to the critical error page [error while processing database query] each time I click on the voting after the first vote.

[zac]

Thought I should add that I am trying this in conjunction with SaWey's Nicer voting mod:

http://forum.coppermine-gallery.net/index.php?topic=40762.0

[zac]

Here is the debug error message in case anyone has a clue how to fix this.. guess I need to hack the SQL ?

While executing query "INSERT INTO cpg149_votes VALUES ('1810', '', '1173818542')" on 0

mySQL error: Duplicate entry '1810-' for key 1

[Nibbler]

this and this should solve that.

[zac]

Hmmmm.. guess it is right in front of me.. I feel like I have tried every combo I could from your instructions 

I replaced this block :

Code Select Expand

// Check if user already rated this picture
$user_md5_id = USER_ID ? md5(USER_ID) : $USER['ID'];
$sql = "SELECT * " . "FROM {$CONFIG['TABLE_VOTES']} " . "WHERE pic_id = '$pic' AND user_md5_id = '$user_md5_id'";
$result = cpg_db_query($sql);
if (mysql_num_rows($result)) cpg_die(ERROR, $lang_rate_pic_php['already_rated'], __FILE__, __LINE__);

with this:

Code Select Expand



$sql = "INSERT INTO {$CONFIG['TABLE_VOTES']} " . "VALUES ('$pic', '$user_md5_id', '$curr_time')";

and this:

Code Select Expand

$sql = "INSERT INTO {$CONFIG['TABLE_VOTES']} " . "VALUES ('$pic', '$user_md5_id', '$curr_time')";
$user_md5_id = USER_ID ? md5(USER_ID) : $USER['ID'];

Thanks for your help.  I dont even know what I am looking at.

[Nibbler]

You remove this code

Code Select Expand


$sql = "SELECT * " . "FROM {$CONFIG['TABLE_VOTES']} " . "WHERE pic_id = '$pic' AND user_md5_id = '$user_md5_id'";
$result = cpg_db_query($sql);
if (mysql_num_rows($result)) cpg_die(ERROR, $lang_rate_pic_php['already_rated'], __FILE__, __LINE__);


and change the query here

Code Select Expand


$sql = "INSERT INTO {$CONFIG['TABLE_VOTES']} " . "VALUES ('$pic', '$user_md5_id', '$curr_time')";


to a REPLACE

Code Select Expand


$sql = "REPLACE INTO {$CONFIG['TABLE_VOTES']} " . "VALUES ('$pic', '$user_md5_id', '$curr_time')";


[zac]

Ahhhhh yes !  Sorry for being dense Nibbler.  Thanks for spelling it out for me ! 

[dan52]

Hi

There are some problems with the solution you're offering here.
If you make those changes - the table data will be updated, and re-votes WILL be allowed.... BUT

1. for the picture, number of votes will continue to increase - even if re-votes are done.
This is wrong, especially since the rating is calculated as division by num_votes + 1 every time.

2. Also, a summary of ratings is kept, and each re-vote is added to that sum.
So in real, the REPLACE you're doing there is not effective: because the rating keeps growing and growing.
(even through the votes table contains the correct number of votes...)

So what you will see is two things:
a. your number of votes per pic will keep growing with each re-vote
b. Your vote average will go 'crazy' with some wierd number which is not an average of nothing really...

To make this problem more 'spicy', you cannot mod the vote summary, because you'd need to first substract the prev vote, and then add the new re-vote and then redo the avg. but you cannot - since you don't know the old vote value (!)

If you'd like to implement this correctly, I can suggest this:

1. Change the votes table to have another column - which is the vote value for each vote.
2. remove the columns for 'number of votes', and 'pic rating' from the pics table!. You don't need those!
3. use SQL commands COUNT() and AVG() functions to get data about pic rating!
-> have SQL do all the 'dirty' work for you for calculations. All the wierd calcs you're doing in ratepic.php are not needed.
Just store the data in SQL, and when you SELECT it back do a nested SELECT to get the AVG and COUNT per picture.

I hope this is helpful,
Dan

PS - I'm working with code in CGP 1.4.13