Whats wrong with my query, should i use WHERE?

So, what am i doing wrong?

This query:

$query = "INSERT INTO table1 (art_nr, article, balance, list_type) 
     VALUES('$art_nr', '$article', '$balance', '$list_type')
     ON DUPLICATE KEY UPDATE balance = sum(balance + '$quantity_ordered');
     UPDATE table2 SET list = 'History' WHERE id = '$id'";

Will give me this error:

Failed to run query: SQLSTATE[HY000]: General error: 1111 Invalid use of group function

This query:

$query = "INSERT INTO table1 (art_nr, article, balance, list_type) VALUES('$art_nr', '$article', '$balance', '$list_type')
        ON DUPLICATE KEY UPDATE balance = sum(balance + '$quantity_ordered') WHERE art_nr = '$art_nr';
        UPDATE table2 SET list = 'History' WHERE id = '$id'";

Will give me this error:

Failed to run query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE art_nr = ‘S2Bygel’; UPDATE purchase_orderlist SET list’ at line 2

UPDATE

This was my first query. With Params:

    //SECURITY
    $params_array= array(
        ':id' => $_POST['formData']['id'],
        ':art_nr' => $_POST['formData']['art_nr'],
        ':article' => $_POST['formData']['article'],
        ':quantity_ordered' => $_POST['formData']['quantity_ordered'],
        ':list_type' => $_POST['formData']['list_type']
    );
     
    //QUERY
    $query = "INSERT INTO table1 (art_nr, article, balance, list_type) VALUES (:art_nr, :article, :balance, :list_type)
                ON DUPLICATE KEY UPDATE balance = balance + VALUES(:quantity_ordered) WHERE art_nr = :art_nr;
                UPDATE table2 SET list = 'History' WHERE id = :id";

The problem with this query is that im running two querys at the same time. and then i will get this error:

Failed to run query: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

SUCCESS

I had to use prepared statements and separate my two querys:

        //SECURITY
    $params_array= array(
        ':art_nr' => $_POST['formData']['art_nr'],
        ':article' => $_POST['formData']['article'],
        ':quantity_ordered' => $_POST['formData']['quantity_ordered'],
        ':list_type' => $_POST['formData']['list_type']
    );
     
    //QUERY
    $query = "INSERT INTO table1
                (art_nr, article, balance, list_type)
                VALUES (:art_nr, :article, :quantity_ordered, :list_type)
                ON DUPLICATE KEY UPDATE
                art_nr = art_nr, article = article, balance = balance + :quantity_ordered, list_type = list_type";
                
    //EXECUTE
    try{ 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($params_array);
    } 
    catch(PDOException $ex){
        die("Failed to run query: " . $ex->getMessage()); 
    }
    
    //SECURITY
    $params_array= array(
        ':id' => $_POST['formData']['id']
    );
     
    //QUERY
    $query = "UPDATE table2 SET list = 'History' WHERE id = :id";
                
    //EXECUTE
    try{ 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($params_array);
        echo "success";
    } 
    catch(PDOException $ex){
        die("Failed to run query: " . $ex->getMessage()); 
    }

Answer

You just want to add the value of $quantity_ordered to balance for the row? Then you don’t need the sum() aggregation function. Just the + operator is enough.

But it seems like you’re doing this in a host language like PHP. You should urgently learn to use parameterized queries! Do not use string concatenation (or interpolation) to get values in a query. That’s error prone and may allow SQL injection attacks against your application.