Rating System With Php

[derekshull]

I have a survey for the people on my site. They rate each question from 0-10 from a select drop down box. There are 5 questions.

 

So I have a table called "survey"

 

If the person's username is already in the table under the "username" column then I want it to add the values to the "voted" column and "total" column. So it'd be adding to whats already there.

 

If the username is not in the "username" column then I want to insert it into the "username" column and add the values to "voted" and "total".

 

I'm still stumped :=/

[Pikachu2000]

Sounds like you probably need MySQL's INSERT INTO . . . ON DUPLICATE KEY UPDATE syntax.

[ManiacDan]

Wait, you want to ADD them?

 

So if I rate something 5 out of 10.

 

Then I come back and rate it 4 out of 10.

 

You want the final score to be 9 out of 10?

[derekshull]

no it'd be 9/20 then

[ManiacDan]

What a weird system.

 

So you want:

INSERT INTO `survey` (`username`, `voted`, `total`)
VALUES ('maniacdan', '5', '10')
ON DUPLICATE KEY UPDATE `voted` = `voted` + VALUES(`voted`), `total` = `total` + VALUES(`total`);

 

Your original problem definition was missing a question ID.

[derekshull]

So i have this right now:

 

$workerusername=$_POST['workerusername'];
mysql_query("INSERT INTO `survey` (`workerusername`, `voted`, `total`) ON DUPLICATE KEY UPDATE `voted` = `voted` + VALUES(`voted`), `total` = `total` + VALUES(`total`)");

 

The script is getting the workers username (who is the person being rated on) and if the workerusername is already in the username column I want it to just add the "total" and "voted" values to the existing ones....

 

If the workerusername is not in the table I want to insert it in and add the values to "total" and "voted"

 

does that makes sense?

[derekshull]

Ok I'm on the right track. It's just not working for some reason. Here's the code:

 

$workerusername=$_POST['workerusername'];
$q1=$_POST['question1'];
$q2=$_POST['question2'];
$q3=$_POST['question3'];
$values = $q1 + $q2 + $q3;
mysql_query("INSERT INTO survey (`username`, `voted`, `total`) VALUES ($workerusername, $values, '30') ON DUPLICATE KEY UPDATE `voted` = $values + VALUES(`voted`), `total` = 30 + VALUES(`total`)");

 

I only have 3 questions in the survey so thats why I add 30 to the total each time there is a duplicate. What am I missing?

 

In the database I made the primary key the username of the person that is being rated....is that correct? or should I make an ID column the primary and make the username column just be normal?

[ManiacDan]

Look at my query again. You swapped things.

[derekshull]

What do you mean i swapped things?

[derekshull]

$workerusername=$_POST['workerusername'];
$q1=$_POST['question1'];
$q2=$_POST['question2'];
$q3=$_POST['question3'];
$addedtogether = $q1 + $q2 + $q3;
$query = "INSERT INTO survey
(username, voted, total)
VALUES ('$workerusername', '$addedtogether', '30')
ON DUPLICATE KEY UPDATE
`voted` = $addedtogether + VALUES(`voted`),
`total` = 30 + VALUES(`total`)";
mysql_query($query) or die("Query: $query
Error: ".mysql_error());

 

Here is what I have just one problem. When I submit a survey and send in 10's on all the questions (so it'd be a score of 30/30). Then next time that I submit a survey I give it all 0. Which means that It should be 60/30 but it makes it 60/0.

 

So apparently it adds totals correct but not the $addtogether variable right. stumpd.

[derekshull]

$workerusername=$_POST['workerusername'];
$addedtogether = intval($_POST['question1']) + intval($_POST['question2']) + intval($_POST['question3']);
$query = "INSERT INTO survey
(username, voted, total)
VALUES ('$workerusername', '$addedtogether', '30')
ON DUPLICATE KEY UPDATE
`voted` = VALUES(`voted`)+$addedtogether,
`total` = VALUES(`total`)+30";
mysql_query($query) or die("Query: $query
Error: ".mysql_error());
echo "<center>Thank you for your feedback!</center>";
?>
Heres my current but still same issue.

[ManiacDan]

My query:

 

ON DUPLICATE KEY UPDATE `voted` = `voted` + VALUES(`voted`), `total` = `total` + VALUES(`total`);

 

 

Your query:

 

 

ON DUPLICATE KEY UPDATE `voted` = $addedtogether + VALUES(`voted`), `total` = 30 + VALUES(`total`)";

 

 

You swapped things. I had `total` and you have 30. I had `voted` and you had $addedtogether.

 

[DarkerAngel]

My query:

 

ON DUPLICATE KEY UPDATE `voted` = `voted` + VALUES(`voted`), `total` = `total` + VALUES(`total`);

There now the context of VALUES(col_name) in the duplicate update make sense to me the stuff derek was posting was throwing me through a loop with it and I was wondering what the point/effect of it was after the update statement.

 

I get it no problem now yeah derek you messed up the statement but removing VALUES() from the update and just doing your

`total` = 30 + `total`

might work, but ManiacDan's method of

`total` = VALUES(`total`) + `total`

seems like a more proper way to do this.