How to Update and Recalculate $_POST Values and Variables in mySQL with PHP

Question

I am having some trouble trying to update some values in mySQL with PHP script. When I try to update the following table row with the following code (the user "Bob" has already inputted values to calculate their total score obtained and percentage but his test3 grade has to be updated to 100 instead of the previous value of 90):

 <html>
 <head></head>
 <body>
  <form class="form" action=""  method="post">
 <?php
   $mysqli = new mysqli('', '', '', '');
   if(isset($_POST['calculate'])) {
   $name = $_POST['name'];
   $test1 = $_POST['test1'];
   $test2 = $_POST['test2'];
   $test3 = $_POST['test3'];
   $obtained = ($test1 + $test2 + $test3);
   $total = 300;
   $percentage = round(($obtained/$total)*100);

   $result = mysqli_query($mysqli, "INSERT INTO table1 (name, test1, 
   test2, test3, totalobtained, totalmarks, percent)
   VALUES ('$name', '$test1', '$test2', '$test3', 
   '$obtained', '$total', '$percentage')");
   }

   $conn = mysqli_connect('', '', '', '');
   if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
   }

   $sql = "UPDATE table1 SET test3='100', totalobtained='$obtained', 
   percent='$percentage' WHERE name='Bob'";


   if (mysqli_query($conn, $sql)) {
     echo "Record updated successfully";
   } else {
     echo "Error updating record: " . mysqli_error($conn);
   }

   mysqli_close($conn);
   ?>

   <table>
    <tr>
     <th>Name of Student*:</th>
     <td><input type="text" name="name"></td>
    </tr>

    <tr>
     <th>Test 1*:</th>
     <td><input type="number" name="test1"></td>
    </tr>

    <tr>
     <th>Test 2*:</th>
     <td><input type="number" name="test2"></td>
    </tr>

    <tr>
     <th>Test 3*:</th>
     <td><input type="number" name="test3"></td>
    </tr>

    <tr>
     <th>Total Marks Obtained:</th>
     <td><?php if(isset($_POST['calculate'])) { echo "$obtained";}?>
     </td>
    </tr>

    <tr>
      <th>Total Marks:</th>
      <td><?php if(isset($_POST['calculate'])) { echo "$total";}?>
     </td>
    </tr>

    <tr>
     <th>Percentage:</th>
     <td><?php if(isset($_POST['calculate'])) { echo "$percentage", 
     '%';}?></td>
    </tr>

    <tr>
     <th><input type="submit" name="calculate" value="Calculate"/>
     </th>
    </tr>
   </table>
   </form>
   </body>
   </html>

It updates test 3 with the test score to 100 from the previous score of 90, however, it doesn't pull the previous test scores to recalculate the total obtained and percentage. As a result, it updates total obtained and percent to 0. Some help would be appreciated as I am pretty new to mySQL and PHP. Thanks!

Previous Table:

+----+-------+-------+-------+-------+----------------+-------------+---------+
| id | name  | test1 | test2 | test3 | totalobtained  | totalmarks  | percent |
+----+-------+-------+-------+-------+----------------+-------------+---------+
|  7 | Bob   |  100  |   100 |  90   |        290     |      500    |      96 |
+----+-------+-------+-------+-------+----------------+-------------+---------+

Updated Table with UPDATE statement:

+----+-------+-------+-------+-------+----------------+-------------+---------+
| id | name  | test1 | test2 | test3 | totalobtained  | totalmarks  | percent |
+----+-------+-------+-------+-------+----------------+-------------+---------+
|  7 | Bob   |  100  |   100 |  100  |        0       |      500    |       0 |
+----+-------+-------+-------+-------+----------------+-------------+---------+

Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, May 04 '17 at 19:48
in case of GET request, this command `$sql = "UPDATE table1 SET test3='100', totalobtained='$obtained', percent='$percentage' WHERE name='Bob'";` will put the totalobtained & percent to 0. because they are not initialized yet. what do you require? — Agam Banga, May 04 '17 at 20:10
@AgamBanga hmm. That is where I'm stuck on. How do I get total obtained and percent to include $test1,$test2 and updated $test3 into its calculation. I'm new to mySQL and PHP so I'm not sure how I would do this? — AmateurCoder, May 04 '17 at 20:17
@AmateurCoder You need to do a `SELECT` query on table with username & check if it exists & then use those data to fill the `percent` & `totalobtained`. Let me know in case of any query — Agam Banga, May 04 '17 at 20:19
@AgamBanga So is it possible to do a "SELECT * WHERE name ='Bob'" within the UPDATE statement, or would I have to initialize it to a separate PHP variable and then call it within my UPDATE statement? — AmateurCoder, May 04 '17 at 20:24
Yes, Please do a seperate `SELECT`, check if the row exists, if yes then get the values & calculate the stuff like percent & then update those — Agam Banga, May 04 '17 at 20:27

score -1 · Answer 1 · answered May 04 '17 at 19:57

-1

Just looking at this I believe the SQL should be

$sql = "UPDATE table1 SET test3='100', totalobtained=$obtained, 
   percent=$percentage WHERE name='Bob'";

You don't need '

Like Alex Howansky points out this is vulnerable to SQL injection.

answered May 04 '17 at 19:57

supajason

660
4
13

I tried this method before and I got a mysql syntax error. "Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' percent= WHERE name='Bob'' at line 1". – AmateurCoder May 04 '17 at 20:00
Echo out $percentage? – supajason May 04 '17 at 20:47
No, that should never be used, as this query is widely open for SQL injection – Nico Haase Oct 01 '21 at 15:35

How to Update and Recalculate $_POST Values and Variables in mySQL with PHP

1 Answers1