Save and update with uniqid in database

Question

I tried to insert the player with a unique id in the database. This model works for me, but if I give it a key update it duplicates it in my database.

Option 1

$id = uniqid();  
$insert_player_query    = mysqli_query($db,"INSERT INTO players (id,nickname,score,time_online,mapname,sid) VALUES ('$id','$player_nickname','$player_score','$player_time','$mapname','$server_id') ON DUPLICATE KEY UPDATE score = score + VALUES(score), time_online = time_online + VALUES(time_online) WHERE id='$id'");

Option 2

Then I tried that, but it doesn't insert anything at all

$id = uniqid();  
$sql=mysqli_query($db, "SELECT * FROM players WHERE nickname='$player_nickname'");

if (mysqli_num_rows($sql) > 0) 
    {
         $insert_player_query2  = mysqli_query($db,"UPDATE players (nickname,score,time_online,mapname,sid) VALUES ($player_nickname','$player_score','$player_time','$mapname','$server_id') WHERE id='$id'");   
                
    } else {
         $insert_player_query   = mysqli_query($db,"INSERT INTO players (id,nickname,score,time_online,mapname,sid) VALUES ('$id','$player_nickname','$player_score','$player_time','$mapname','$server_id') WHERE id='$id'");   
    }

`uniqueid()` generates a string. When your ID field is numeric, it won't work. — Markus Zeller, Jul 20 '20 at 09:24
Make the ID field a varchar or char with fixed length, if your unique ID always having the same length. — Markus Zeller, Jul 20 '20 at 09:29
I forgot to specify, I did this in the database. `id varchar (200) ` But it still doesn't work. — MrDons, Jul 20 '20 at 09:30
Is this query correct? "mysqli_query($db,"INSERT INTO players (id,nickname,score,time_online,mapname,sid) VALUES ('$id','$player_nickname','$player_score','$player_time','$mapname','$server_id') WHERE id='$id'");" In my opinion the where contidion from the end is wrong — Paweł Miłosz, Jul 20 '20 at 09:31
Your quotes are wrong. Check them over. Also use prepared statements to prevent SQL injections. — Markus Zeller, Jul 20 '20 at 09:32
Option 2 won't work for updating, because you're still trying to use a new ID. You need to retrieve the ID from your SELECT query if it finds a matching row, and use that. And in there, your INSERT query doesn't need that WHERE on the end, as @PawełMiłosz said above. — droopsnoot, Jul 20 '20 at 09:32

mahdi marjani moghadam · Answer 1 · 2020-07-20T14:24:45.610

0

You should check field and use:

CREATE TABLE `players` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nickname` varchar(100) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `time_online` varchar(100) DEFAULT NULL,
  `mapname` varchar(100) DEFAULT NULL,
  `sid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;


INSERT INTO players (id,nickname,score,time_online,mapname,sid) VALUES ('1','player_nickname','1','1','mapname','1') 
ON DUPLICATE KEY UPDATE score = score + VALUES(score), time_online = time_online + VALUES(time_online)

Please try it.

edited Jul 20 '20 at 14:24

answered Jul 20 '20 at 09:31

mahdi marjani moghadam

721
6
19

I added it to the base structure but nothing happened. – MrDons Jul 20 '20 at 10:24
INSERT INTO player (id,nickname,score,time_online,mapname,sid) VALUES ('$id','$player_nickname','$player_score','$player_time','$mapname','$server_id') ON DUPLICATE KEY UPDATE score = score + VALUES(score), time_online = time_online + VALUES(time_online) – mahdi marjani moghadam Jul 20 '20 at 12:14
He duplicates it – MrDons Jul 20 '20 at 12:47
id should not be auto-increment – mahdi marjani moghadam Jul 20 '20 at 14:17

score 0 · Answer 2 · answered Jul 20 '20 at 10:06

0

Remove "where" condition. It does not make any sense in "INSERT" statement.

answered Jul 20 '20 at 10:06

Bhupinder Singh

132
11

I modified, but it inserts only 1 single player. – MrDons Jul 20 '20 at 10:23
you need to use batch insert https://stackoverflow.com/questions/5526917/how-to-do-a-batch-insert-in-mysql – Bhupinder Singh Jul 28 '20 at 11:29

Save and update with uniqid in database

2 Answers2