PHP/SQL script to increment value of a record field is not updating the record

Question

HTML:

<form>
    <input type="radio" name="grade" value=95 /> A<br />
    <input type="radio" name="grade" value=85 /> B<br />
    <input type="radio" name="grade" value=75 /> C<br />
    <input type="radio" name="grade" value=65 /> D<br />
    <input type="radio" name="grade" value=50 /> F
</form>

PHP:

if (isset($_POST['grade'])) {
                    $name = $_POST['name'];
                    $grade = $_POST['grade'];
                    $sql = "UPDATE grade SET 
                    total=total+'$grade',
                    numvotes=numvotes+1 WHERE
                    name='$name'";

Hi everyone... I'm working on a project to add grades associated with names on a menu. My HTML code for the radial menu for the grade is above and my relevant SQL is shown as well. I want to add a NUMBER VALUE from the grade onto the "total" in my SQL database and increase the number of votes by 1. I'm not sure if my syntax is correct because the database neither gets an addition to its votes or grade total. Thanks!

EDIT: Part of the reason why I'm confused that this doesn't work is b/c when I go into the mySQL console, I can do an almost identical command (where instead '$grade' is a number) and it works. In the least I should get an error or maybe the numvotes should increase, but nothing.

EDIT2: Credit to Radu for catching this. My name menu doesn't function properly. After using $die after my SQL statement, I found that the names being selected from the dropdown menu were being interpreted as integers, not names. It is to be populated by the SQL names in the database. Here is my code.

<?php
    $query = mysql_query("SELECT name, id FROM grade");
    echo "<select name='name'>";
        while ($temp = mysql_fetch_assoc($query)) {
            echo "<option value='".$temp['name']."'>".$temp['name']."    </option>";
        }
    echo "</select>";
?>

EDIT3: After changing $temp['id'] to $temp['name'], I found that my die($sql) now reads:

UPDATE grade SET total=total+'95', numvotes=numvotes+1 WHERE name='charlie'

So the name is going in, but it's STILL not getting updated. Ideas?

Why not save each submission with name and score, then you can count how many scores were submitted by name and total the scores with one simple query. — NightHawk
– NightHawk, Commented Jun 7, 2011 at 19:41
A minor note - the value attribute in your <input> tags should be quoted. In fact, all HTML attributes get quotes. — Surreal Dreams
– Surreal Dreams, Commented Jun 7, 2011 at 19:42
@ryan: A good point, but then there would be a LOT of redundancy in my database. As it built up, there would be thousands of grades associated with the same name. — tnw
– tnw, Commented Jun 7, 2011 at 19:46

rid · Accepted Answer · 2011-06-07 20:21:47Z

3

You're using names as strings in the SQL query. So change the following:

echo "<option value='".$temp['id']."'>".$temp['name']."</option>";

to:

echo "<option>".htmlspecialchars($temp['name'])."</option>";

For live projects, always use htmlspecialchars() when echo()ing something to the browser, and always use mysql_real_escape_string() when composing SQL queries from user input.

For example, in a live project, you should always use $name = mysql_real_escape_string($_POST['name']) instead of simply $name = $_POST['name'].

edited Jun 7, 2011 at 20:21

answered Jun 7, 2011 at 20:10

rid

64k31 gold badges159 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rid Over a year ago

You're welcome. Just don't forget to use htmlspecialchars() and mysql_real_escape_string() in real, live projects. There will only be benefits from doing this.

BlueRaja - Danny Pflughoeft Over a year ago

@Tory @Radu: Actually in real, live projects, you shouldn't be concatenating SQL strings at all; you should use prepared statements instead. There is then no chance of you accidentally forgetting to escape a string.

Dalen · Accepted Answer · 2011-06-07 19:38:19Z

3

remove , after

numvotes=numvotes+1

in your query

answered Jun 7, 2011 at 19:38

Dalen

9,0264 gold badges49 silver badges53 bronze badges

1 Comment

tnw Over a year ago

That seems like it should work, but I still get the same behavior. I have changed that, however. Thank you.

NightHawk · Accepted Answer · 2011-06-07 19:47:06Z

1

USERS
-----
id AUTO_INCREMENT
name

VOTES
-----
id AUTO_INCREMENT
user_id
grade

INSERT INTO votes (user_id, grade) VALUES (1, 95);
INSERT INTO votes (user_id, grade) VALUES (1, 85);
INSERT INTO votes (user_id, grade) VALUES (2, 75);

Then to get vote count for first user:

SELECT count(*) total_votes FROM votes WHERE user_id = 1;

And to get score:

SELECT sum(grade) total_score FROM votes WHERE user_id = 1;

This is untested, but should get you on the right track.

answered Jun 7, 2011 at 19:47

NightHawk

3,7418 gold badges40 silver badges56 bronze badges

4 Comments

NightHawk Over a year ago

It basically stores information about the user. Then if anything ever changes about a user, such as the name, your queries will still be valid and you'll get all the votes from that user. It prevents data anomalies.

user212218 Over a year ago

This might arguably be a better way to store the data; unfortunately it does not address the OP's problem that his SQL does not appear to be getting executed on the database.

NightHawk Over a year ago

@Phoenix true, but storing it this way does ultimately give me him the results he wants.

tnw Over a year ago

The code I originally posted works, though the way I work my dropdown menu is flawed. Please see above.

bumperbox · Accepted Answer · 2011-06-07 20:30:05Z

1

it might just be an oversight in your example, but the tag should be

<form method="post">

otherwise this condition will never trigger, as forms default to using GET method

if (isset($_POST['grade'])) {

answered Jun 7, 2011 at 20:30

bumperbox

10.2k6 gold badges46 silver badges66 bronze badges

Collectives™ on Stack Overflow

PHP/SQL script to increment value of a record field is not updating the record

4 Answers 4

2 Comments

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related