Update table row and increase row data with + 1

Question

I wanna make Vote script for my site it right now it looks like this //Input form

<form action='votes.php' method='post'>
<input type='hidden' name='buttonUp' value='1'/>
<input type='submit' name='submitVote' value='Vote Up'/>
</form>

And php file which process this

isset($_POST['submitVote'])) {  

$sql="INSERT INTO answers 
SET up = '$_POST[buttonUp]',
questionId = (SELECT id FROM quesitons WHERE id = '$_POST[id]');
";

Sql table has fields, id, answer, questionId, user, date, ip, up, down So, if somebody like answer with specific ID, click on Vote Up, and in filed UP should be updated value + 1. Few hours i am about this, and don't know how to make it. Also i suppose that there should be UPDATE answers SET... but i try that also, and nothing... Thank you

Table Questions

CREATE TABLE IF NOT EXISTS `pitanja` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pitanje` text NOT NULL,
  `korisnik` varchar(255) NOT NULL,
  `datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Here is Table Answers

CREATE TABLE IF NOT EXISTS `answers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `answer` text COLLATE utf8_unicode_ci NOT NULL,
  `questionId` int(11) NOT NULL,
  `user` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `up` int(11) NOT NULL,
  `down` int(11) NOT NULL,
  PRIMARY KEY (`id`)
  )

Here is tables http://i.imgur.com/vFp9Iyb.png - Question and http://i.imgur.com/sedlkza.png Answers. — bobouch, Jan 29 '14 at 20:47

score 1 · Accepted Answer · edited May 23 '17 at 11:56

1

First off:

1) Never insert raw user data straight into your database. (See here for more info)

2) You should be supplying a hidden field in your form with the question ID.

Try something like this:

<form action='votes.php' method='post'>
    <!-- The "questionID" value here (12345) is just an example. -->
    <!-- Substitute the 12345 for the actual ID of this answer. -->
    <input type='hidden' name='questionID' value='12345'/>
    <input type='hidden' name='buttonUp' value='1'/>
    <input type='submit' name='submitVote' value='Vote Up'/>
</form>

Then...

isset($_POST['submitVote'])) {  

    // Absolute MINIMUM data santitation here as an example.
    // You should do more. And NOT use msql_* functions as they are deprecated
    $sanitizedQuestionID = mysql_real_escape_string($_POST['questionID']);

    // Edited Query. This one works!
    $sql = "UPDATE `answers`
            SET `up` = up + 1
            WHERE `questionId` = $sanitizedQuestionID;";

edited May 23 '17 at 11:56

Community

1
1

answered Jan 29 '14 at 19:53

TunaMaxx

1,782
12
18

TunaMaxx - could you please check this, not working... WIth this UP field is not updated... – bobouch Jan 29 '14 at 20:50
I'd love to, but we need to know your database schema. Kind of making wild guesses without that info. – TunaMaxx Jan 29 '14 at 21:03
Updated... Take a look. – bobouch Jan 29 '14 at 22:27
@bobouch, I've moved your edit to the main question. When you have additional information regarding the question, please put it in the question's post. It should not go in the answers section (and especially not as an edit to someone else's answer). – valverij Jan 29 '14 at 22:34
I've very slightly modified the query, and it works with your table info. Just verify that your equivalent of *$sanitizedQuestionID* is an integer if you have any problems. Give it a try now! – TunaMaxx Jan 29 '14 at 23:24
Tuna - i am not quite sure what i need to do more. I am sure that your code is working, but for me still not. Maybe because of my not good enough english or whatever, but not is going to update. And also i didnt understand this _ Just verify that your equivalent of $sanitizedQuestionID is an integer if you have any problems._ – bobouch Jan 30 '14 at 09:55
Hey TunaMaxx - I've changed questionID value from 12345 to only 1 and now, it update me UP field but only for ex. Firsta answer, so if I vote up for second answer, that vote goes to First... Any idea how to Up vote for every answer, not only for the first? – bobouch Jan 30 '14 at 11:24
The 12345 was just an example. You need to populate that field with the **answer ID** for that answer. You should already have that ID collected before you generate your "Vote UP" form. – TunaMaxx Jan 30 '14 at 17:18

score 1 · Answer 2 · answered Jan 29 '14 at 19:57

On MySQL side you can leverage ON DUPLICATE KEY clause of INSERT statement

INSERT INTO answers (questionId, up) VALUES (?, 1)
ON DUPLICATE KEY UPDATE up = COALESCE(up, 0) + 1

For this to work properly you have to have a UNIQUE constraint on questionId

Here is SQLFiddle demo

Update table row and increase row data with + 1

2 Answers2