mySQL insert a new row and allow duplicate values-CodePudding

I've been searching in vain to find the answer to this question and maybe it's because the search term is not one best expressed in a few words.

I have a table to store the names and scores of people who play a game I created. I would like to be able to allow a new instance of an entry each time even if the name already exists. For example, the top 10 scores could all be from player John, and all the scores could be different.
John - 10
John - 9
John - 8 etc etc

However, what currently happens is that when John scores 11, all entries with the name John get updated to 11.

The statement I am using is:

<?php


if(isset($_GET['username']))
{
    //Include the connection to the database
    include("connection.php");
    $link = getDBConnection();


    //Define the local variables
    $username = $_GET['username'];
    $score = $_GET['score'];
    
    
    $query2 = "INSERT INTO `scoreboard` (`ID`, `username`, `score`) VALUES (NULL, '".mysqli_real_escape_string($link, $username)."', '".$score."');";
    $result = mysqli_query($link, $query2);       
    

}else{
    echo "Error: Username has not been recieved in global POST array";
}

Please can someone help me?

CodePudding user response：

I've coded the situation on dbFiddle as shown and cannot reproduce the errror.
The first thing to check is the table definition. As you insert null as ID I'm assuming that you have an auto_increment column. (It is simpler to not specify the column than to specify with null).
NB Your code is at risk from SQL injection because you use the score without checking anything. The following line would accept any valid number and would stop with an error if the value is not numeric.
$score = 1 * $_GET['score'];

create table scoreboard (
id int not null primary key auto_increment,
username varchar(25),
score int);

✓

insert into scoreboard ( username,score) values 
('John',8),('John',9),('John',10);

✓

insert into scoreboard (id, username,score) values (null,'John',11);

✓

select * from scoreboard;

id | username | score
-: | :------- | ----:
 1 | John     |     8
 2 | John     |     9
 3 | John     |    10
 4 | John     |    11

db<>fiddle here