Overwrite existing score if new score is higher

Question

On submitting (ID, username, score), I only want the database to update if the score is higher than the usernames previous score.

So far I have

$sql = mysqli_query($con, "INSERT INTO $db_name.$db_table (ID, name, score)
                               VALUES ((ID, name , score) IF $ID=ID & $name=name & '$score'>score);");

Full code is

if(isset($_GET['ID']) & isset($_GET['name']) & isset($_GET['score'])){

$con = mysqli_connect($host, $db_username, $db_password, $db_name);


$ID = strip_tags(mysqli_real_escape_string($con, $_GET['ID']));
$name = strip_tags(mysqli_real_escape_string($con, $_GET['name']));
$score = strip_tags(mysqli_real_escape_string($con, $_GET['score']));

$sql = mysqli_query($con, "INSERT INTO $db_name.$db_table (ID, name, score)
                           VALUES ((ID, name , score) IF $ID=ID & $name=name & '$score'>score);");


if($sql){

    //The query returned true - now do whatever you like here.
    echo 'Your score was saved. Congrats!';
    
}else{
 
    //The query returned false - you might want to put some sort of error reporting here. Even logging the error to a text file is fine.
   echo("<br>Error description: " . mysqli_error($con));
}

mysqli_close($con);//Close off the MySQLI connection to save resources.

**Warning**: You are wide open to [SQL Injections](https://www.php.net/manual/en/security.database.sql-injection.php) and should use parameterized prepared statements instead of manually building your queries. They are provided by [PDO](https://www.php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](https://bobby-tables.com/). — astentx, Feb 03 '21 at 13:06
would you want to record multiple row of scores for the same user, or just one per user, and replace the lower value to new, higher one? — ariefbayu, Feb 03 '21 at 13:07
you didn't actually explain what the problem is. You said what you want, but didn't relate that to the current code at all. What is going wrong exactly? — ADyson, Feb 03 '21 at 13:09
@ariefbayu yes, currently the scoreboard shows top 20 scores. But top 5 are all by the same user. So i just one the top user to have their score overwritten by a new higher one if achieved. — Jaydog182, Feb 03 '21 at 13:10
@ADyson error code i am getting is "Error description: 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 'IF Jaydog182=ID & jaydob=name & '913'>score)' at line 2" — Jaydog182, Feb 03 '21 at 13:11
Ah ok. SQL doesn't have `IF` statements. Well, it does, but you can't put them within another query. Gordon's answer below shows you a sensible way to approach this requirement. In future, when you're asking here, please include any error messages you're getting. It makes your question a lot clearer. See [How To Ask](https://stackoverflow.com/help/how-to-ask) for more info. Also please take the [tour](https://stackoverflow.com/tour) (I can see by the lack of badge in your profile that you haven't...it should have been recommended to you when you signed up to the site). — ADyson, Feb 03 '21 at 13:12
@ADyson Got Gordons answer to work. I'll have a look through the tour as suggested. Thanks for the help — Jaydog182, Feb 03 '21 at 13:31

score 3 · Accepted Answer · answered Feb 03 '21 at 13:08

You seem to want on duplicate key update. You want one row per name, so you need a unique index or cosntraint on name:

CREATE UNIQUE INDEX unq_table_name ON $db_name.$db_table(name);

Then you can use:

INSERT INTO $db_name.$db_table (ID, name, score)
    VALUES (ID, name, score)
    ON DUPLICATE KEY UPDATE score = GREATEST(score, VALUES(score));

The use of $db_name.$db_table is highly problematic. It suggests that a single entity (whatever should be in the table) is spread through multiple tables. That is really bad data modeling.

In addition, it potentially opens the code to SQL injection and hard to debug syntax errors. I would recommend that you re-think your database design so you have scores in only one table, perhaps called user_scores or something more intelligible than $db_name.$db_table.

Overwrite existing score if new score is higher

1 Answers1