Make Sum in SQL and save it to another table in SQL with PHP?

Question

Hi I had searched a lot on this site for help, but more I search more I'm confused.

I have one table competition where there is field ID(primary key) Player *TeamName*(3 players in a team) and Score(players)

      competition                     TeamCompetition

---

Player : TeamName : Score       :  TeamName  :  TeamScore
------ : -------- : -----       :  ----------------------
 Tom   :   team1  :   50        :   team1    :  50+66+35=151
Brian  :   team1  :   66        :   team2    :  54+85+...etc
 Tim   :   team1  :   35        :   
Calvin :   team2  :   54        :
Jerry  :   team2  :   85        :

etc.

I have managed to make SUM in SQL Like so:

$result1 = mysql_query("SELECT SUM(Score) AS team1 FROM competition WHERE TeamName='team1' ");

I tried to save that variable $team1 to new table called TeamCompetition and where sum of one team is saved like one number with name of a team

$row1= mysql_fetch_array($result1);

$team1= $row1['team1'];

$sql1=("INSERT INTO TeamCompetition (TeamScore)
        VALUES '$_POST[team1]' 
        WHERE TeamName='team1' ");

But that wont work, can anybody suggest me how to deal with this code, OR is there another more eleganth way to solve it? If I echo it without $sql1 it shows result why can't I save it...?

Answer 1

The basic solution to your SQL syntax issue is:

$sql1=("INSERT INTO TeamCompetition (TeamScore, TeamName)
    VALUES ('".$_POST[team1]."','team1'");

As there is no row currently in the DB woth the teamname, you should create it. Also, notice that the $_POST is an array, not handled well in evaluated scripts.

However, I assume you'd like to updtae this as you get scores:

$sql1=("UPDATE TeamCompetition set TeamScore = '".$_POST[team1]."'
    WHERE TeamName = 'team1'");

But there is one neat trick in MySQL, the INSERT.... ON DUPLICATE KEY UPDATE feature:

$sql1=("INSERT INTO TeamCompetition (TeamScore, TeamName)
    VALUES ('$_POST[team1]','team1'
    ON DUPLICATE KEY UPDATE TeamScore = '".$_POST[team1]."'" );

There is however one thing I can't go by: this code reeks of SQL injection

• either use PDO (generally best solution)
• and/or don't use any data coming from untrusted, or easy to manipulate sources wihtout verifying, and properly handling them (worse solution, but way better than nothing)

The least effort protection for this code (as long as these functions are still supported, noted by the awesome NullPointer) can be using mysql_real_escape_string, but still, this whole thing is going to be deprecated, so it should not be used for new developments:

$sql1=("INSERT INTO TeamCompetition (TeamScore, TeamName)
    VALUES ('".mysql_real_escape_string($_POST[team1])."','team1'
    ON DUPLICATE KEY UPDATE TeamScore = '".$_POST[team1]."'" );

So if you have to patch up an old code, that is going to stay that way, you can decide to use this, but only after evaluating the possibilities of using PDO! If there is only a slight possibility of using PDO, then that is the way to go (with courtesy of the PHP PDO documentation):

<?php
try {
    //open connection, this is different than in the old functions
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

    //***running query
    //**step1: create statement
    $stmt = $dbh->prepare("INSERT INTO TeamCompetition (TeamScore, TeamName)
    VALUES (:teamScore,:teamName)
    ON DUPLICATE KEY UPDATE TeamScore = :teamScore "); //notice parameters prefixed with ':'

    //**step2: bind values
    $stmt->bindValue(':teamScore', $_POST[team1]);
    $stmt->bindValue(':teamName', 'team1');

    //**step3: exexcute statement
    $stmt->execute();

    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?> 

Answer 2

You could just do it all in one fowl swoop. No need to pass information via PHP at all.

INSERT INTO TeamCompetition (TeamName, TeamScore) VALUES (SELECT TeamName, SUM(Score) FROM competition GROUP BY TeamName)

you need to validation input and your code is vunerable to sql injection check How to prevent SQL injection in PHP?

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use PDO or MySQLi