How to add value to existing value using MySQL?

Question

hi dear friends yesterday I asked about a teachers rating system but did not get any idea please help me. i am making a teacher rating system where i want to rate every teacher by making text boxes in front of the name of each teacher .the number that the user will add in text box for a specific teacher is to be added to a current rate of teacher.but the code is not working properly.when i add values through text boxes it adds the value from the last text box to all rows.for example for the first teacher i want to add 2 ,for the second i want to add 4 and for the 3rd teacher i want to rate 3. so instead of 2 and 3 all the rating of all teachers are incremented by 3.how to solve the problem?please help i am giving code of 2 pages m.php and n.php

m.php

<form action="n.php" method="post" enctype="multipart/form-data">
<table width="642" height="215" border="10" align="left" cellspacing="0"  >
    <tr>
        <th class="style5">Teacher ID</th>
        <th width="90" class="style5">Teacher Name</th>
        <th width="127" class="style5">Teacher Registration</th>
        <th width="135" class="style5">Teacher Qualification</th>
        <th width="92" class="style5">Teacher Subject</th>
        <th width="92" class="style5">Action</th>
      </tr>
    <?php
    include 'conn.php';

    $sql = "SELECT * FROM teacher ";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        while($row = $result->fetch_array()){
        $id=$row['tid'];
        ?>
        <tr>
            <td height="50" align="center" class="style5"><?php echo $row['tid'];?></td>
            <td align="center" class="style5"><?php echo $row['tname'];?></td>
            <td align="center" class="style5"><?php echo $row['treg'];?></td>
            <td align="center" class="style5"><?php echo $row['qualification'];?></td>
            <td align="center" class="style5"><?php echo $row['subject'];?></td>
             <td align="center"> <input type="text" name="rating">
          </td>
        </tr>
        <?php
        }   
    }else{
        echo "<center><p><font size=10/> No Records</p></center>";
    }

    $conn->close();
    ?><tr><td colspan="6">
    <input type="submit" name="submit" value="Enter"></td></tr>
  </table>
</form>

n.php

<?php
mysql_connect("localhost","root","") or die ("couldnt connnect to server");
mysql_select_db("project")  or die ("couldnt connnect to database");


include 'conn.php';
if(isset($_POST['submit']))
{
    $sql = "SELECT * FROM teacher";
    $result = $conn->query($sql);
    if($result->num_rows > 0){
        while($row = $result->fetch_array()){
            $id=$row['tid'];
            $newvalue=$_POST['rating'];
            $sql_update="UPDATE teacher set hits =  hits +$newvalue where tid=".$id.""; 
            mysql_query($sql_update) or die(mysql_error());
            header("location:m.php");
        }
    }
}

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jan 03 '16 at 16:58
Possible duplicate of [how to add number to current value in phpmysql](http://stackoverflow.com/questions/34567943/how-to-add-number-to-current-value-in-phpmysql) — wogsland, Jan 03 '16 at 16:58
Your issue is that you have `n` number of `name="rating"` that is created in a loop, where the last one will overwrite all previous values. Instead you need to build as an array, with the `tid` as the key - `name="rating[$row['tid']]"`. Now in your `UPDATE` you can use that key/value pair to specify what to update — Sean, Jan 03 '16 at 17:07
Well, first thing to do is to change - `` to ``, so now `rating` is now an array instead of a single field/value. — Sean, Jan 03 '16 at 17:15
And then in your update code, you change `$newvalue=$_POST['rating'];` to `$newvalue=$_POST['rating'][$row['tid']]`, so that your value is the one for that specific `tid`. — Sean, Jan 03 '16 at 17:17
You also need to figure out why you are doing both `$conn->query();` AND `mysql_query();` in the same code/file (which is highlighted in [this comment](http://stackoverflow.com/questions/34579114/how-to-add-value-to-existing-value-using-mysql#comment56902932_34579114)) You should also read up on [How can I prevent SQL-Injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Sean, Jan 03 '16 at 17:22
thanks a lot dear friend cant explain my feelings this time u solved my problem .many prayers for u — Easha Khan, Jan 03 '16 at 17:28
You should add as an answer @Sean you will have my upvote since you was the first one. — Jorge Campos, Jan 12 '16 at 03:31
@EashaKhan you should accept your questions answers if they are correct or add a comment to the answer if it is not quite right. — Michael, Jan 22 '16 at 10:56

score 0 · Answer 1 · answered Jan 12 '16 at 04:04

Like this ... give rating name array with id from table and update the record with the right value and id ... but never do this way for username and password case ...

<form action="n.php" method="post" enctype="multipart/form-data">
<table width="642" height="215" border="10" align="left" cellspacing="0"  >
<tr>
    <th class="style5">Teacher ID</th>
    <th width="90" class="style5">Teacher Name</th>
    <th width="127" class="style5">Teacher Registration</th>
    <th width="135" class="style5">Teacher Qualification</th>
    <th width="92" class="style5">Teacher Subject</th>
    <th width="92" class="style5">Action</th>
</tr>
<?php
include 'conn.php';
$sql = "SELECT * FROM teacher ";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_array()){
    $id=$row['tid'];
    ?>
    <tr>
        <td height="50" align="center" class="style5"><?php echo $row['tid'];?></td>
        <td align="center" class="style5"><?php echo $row['tname'];?></td>
        <td align="center" class="style5"><?php echo $row['treg'];?></td>
        <td align="center" class="style5"><?php echo $row['qualification'];?></td>
        <td align="center" class="style5"><?php echo $row['subject'];?></td>
         <td align="center"> <input type="text" name="rating[<?php echo $id; ?>]">
      </td>
    </tr>
    <?php
    }   
}else{
    echo "<center><p><font size=10/> No Records</p></center>";
}

$conn->close();
?><tr><td colspan="6">
<input type="submit" name="submit" value="Enter"></td></tr>
</table>
</form>

<?php
mysql_connect("localhost","root","") or die ("couldnt connnect to server");
mysql_select_db("project")  or die ("couldnt connnect to database");
include 'conn.php';
if(isset($_POST['submit']))
{
$sql = "SELECT * FROM teacher";
$result = $conn->query($sql);
if($result->num_rows > 0){
    while($row = $result->fetch_array()){
        $id=$row['tid'];
        $newvalue=$_POST['rating'];
        if(isset($newvalue[$id])) {
            $new_temp_value = $newvalue[$id];
            $sql_update="UPDATE teacher set hits =  hits +$new_temp_value where tid=".$id."";
            mysql_query($sql_update) or die(mysql_error());
        }
    }
}

header("location:m.php");
}

How to add value to existing value using MySQL?

1 Answers1