UPDATE mysqli with SUM of unique row value string value-CodePudding

Sorry by this simple question. I found many many many (almost infinite) topics of how update join values row row and get total value but no one helped me yet.

Actually I have this SQL

  <? php
  $idu = '135';
  $tvt = '1';

  $conn = mysqli_connect($servername, $username, $password, $database);

 $sql = "INSERT INTO user (idu,tvt)
 VALUES ('$idu','$tvt')";
 if (mysqli_query($conn, $sql)) {
 //   echo "New record has been added successfully !";
 } else {
 //   echo "Error: " . $sql . ":-" . mysqli_error($conn);
  echo "Connection Error.";
 }
 mysqli_close($conn);
 }
 ?>

Result:

 ---------- ---- 
| idu    | tvt | 
 ---------- ---- 
| 135       | 1 |  
| 135       | 1 |
| 135       | 1 |
 ---------- -----

But this just add a new duplicated row, how I can update and sum values?

Expected:

 ---------- ---- 
| idu    | tvt | 
 ---------- ---- 
| 135       | 3 |  
 ---------- -----

thx

CodePudding user response：

You can try this query that use the ON DUPLICATE KEY UPDATE (refer: https://www.javatpoint.com/mysql-insert-on-duplicate-key-update)

$sql = "INSERT INTO user (idu, tvt) VALUES(".$idu.", ".$tvt.") ON DUPLICATE KEY UPDATE tvt = tvt   ".$tvt.";

Just need to make sure that idu set as key and you've cleared of previous duplicate record. And I'm unable to test this code on online tools so not guarantee this will work.

CodePudding user response：

Example of an SQL script to create the summary

$mysqli = mysqli_connect($servername, $username, $password, $database);
mysqli_query( $mysqli, "SET NAMES UTF8" );
            
$sql_select = "SELECT SUM(tvt) AS sum_tvt FROM user GROUP BY idu";
$sql_query = mysqli_query( $mysqli, $sql_select);
$sql_fetch = mysqli_fetch_assoc( $sql_fetch );
echo "Summay is ".$sql_fetch['sum_tvt'];
mysqli_close($mysqli);

Example of an SQL script to upadte idu value

$sql_update = "UPDATE tvt SET tvt   "'.$tvt.'"
        WHERE idu= '".$id."' ";
$sql_query = mysqli_query($conn,$sql);