Ratings System Code Not Writing To Database

[derekshull]

I need help with this php survey script.

 

It's supposed to check and see if there are any usernames that are already in the system and if there are then it just adds the values "voted" and "total" to the existing values that are there.

 

If the username is not in the database it will insert that name and add the values.

 

What I have right now is this:

 

$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`)");

 

What am I doing wrong? My primary key in the database is ID and then I have username, voted, and total as varchar.

[Psycho]

1. How do you add varchars? Even though it may work, it's not good process. Change the fields to ints (or floats)

2. Since those fields are varchar's - which they shouldn't be - you would need to enclose the values in quotes. Again, change the fields to ints (or floats).

3. You have no error handling of your query.

 

You SHOULD really change the field types. But, this would work with what you have

$workerusername = mysql_real_escape_string(trim($_POST['workerusername']));
$values intval($_POST['question1']) + intval($_POST['question2']) + intval($_POST['question3']));

$query = "INSERT INTO survey
		  (username, voted, total)
	  VALUES ('$workerusername', '$values', '30')
	  ON DUPLICATE KEY UPDATE
		  `voted` = $values + VALUES(`voted`),
		  `total` = 30 + VALUES(`total`)";
mysql_query($query) or die("Query: $query<br>Error: ".mysql_error());

[derekshull]

I got this error:

 

Parse error: syntax error, unexpected T_STRING in/home/content/17/9932517/html/1511project/modules/php/php.module(80) : eval()'d code on line 34

 

Its on the $values line.

[derekshull]

I changed the varchar to int for the numbers...should I put that back?

[derekshull]

Nevermind I switched it back to getting $username and $values like I had it last time and it works

 

Thanks for the code.

[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>";
?>

 

I have this right now but something is wrong. when i submit one survey i put 30/30 and when i look at the database it does that.

 

Then the next survey I submit I put 0/30...it will add the 30 to the total but it changes the value of "voted" to 0....but it should be30/60.

 

Then I submit another survey and from then on the total amount doesn't change it stays at 60 and the "voted" value is whatever I submit from that particular form. So if I submit 25/30 it would be 25/60 in the database. Then if I submit 12/30 it shows up as 12/60 in the database....what is wrong?

Edited October 11, 2012 by derekshull

[DarkerAngel]

I'm not sure how VALUES(col) is supposed to work; If the column structure is INT (which it's working with numbers so it should be) then I just normally do:

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

 

Not sure if some change has occurred in which this format depreciated, but it is how I do mine...

[Psycho]

Right, if the fields are ints (which they should be) then the VALUES() is not needed. BUt, I just realized you said this

 

My primary key in the database is ID and then I have username, voted, and total as varchar.

 

The ON DUPLICATE KEY only works if the INSERT data will cause a duplicate in a field set to be unique. Has the 'workerusername' field been set up to be unique? If so, give this a try:

 

$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` = `voted` + $addedtogether,
		 `total` = `total` + 30";
mysql_query($query) or die("Query: $query<br>Error: ".mysql_error());

echo "Query run: $query"; //Leave this uncommented for debugging
echo "<center>Thank you for your feedback!</center>";

 

Note, by not escaping the $_POST['workerusername'] you are opening yourself up to SQL Injection. Besides you should be passign an ID and not a username.