mysql Update dupa nume

Discutii despre script-uri si coduri PHP-MySQL, precum si lucru cu XML in PHP.
mrdons
Mesaje:2

mysql Update dupa nume

Salutari,

Incerc de ceva timp sa fac sa imi salveze informatiile jucatorului in baza de date, o data inserate vreau atunci cand gaseste din nou acel jucator sa ii adauge scor, timp, harta .
Insa acesta imi duplica continutul in loc sa adauge dupa nume (nickname='$player_nickname).
Ce este de facut ?
Acesta este codul :

Cod: Selectaţi tot

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')  nickname='$player_nickname'");   

            } else {
                 $insert_player_query   = mysqli_query($db,"INSERT INTO players (id,nickname,score,time_online,mapname,sid) VALUES ('','$player_nickname','$player_score','$player_time','$mapname','$server_id')");   
            }
		}
Codul complet:

Cod: Selectaţi tot

<?php 
include('/db.php'); 

$server_query	= mysqli_query($db,"SELECT id,ip,game FROM servers WHERE UNIX_TIMESTAMP()-last_update>180 ORDER BY rank_pts DESC");
while($server_row = mysqli_fetch_assoc($server_query)){
	$server_id					= $server_row['id'];
	$server_ip					= $server_row['ip'];
	$server_game				= $server_row['game'];
	$last_update                = time();
	
    require_once('/GameQ.php'); 
$servers = array(
	array(
		'id' => $server_id,
		'type' => $server_game,
		'host' => $server_ip,
	)
);
$gq = new GameQ();
$gq->addServers($servers);
$gq->setOption('timeout', 4); // Seconds
$gq->setFilter('normalise');
$results = $gq->requestData();

foreach($results as $data){
	

	if($data['gq_online'] == "1"){
		$hostname			= mysqli_real_escape_string($db,$data['gq_hostname']);
		$mapname			= mysqli_real_escape_string($db,$data['gq_mapname']);
		$num_players		= mysqli_real_escape_string($db,$data['gq_numplayers']);
		$max_players		= mysqli_real_escape_string($db,$data['gq_maxplayers']);
		$players			= $data['players'];
		
        $hostname		= (!empty($hostname)) ? $hostname : '---';
        $mapname		= (!empty($mapname)) ? $mapname : '---';
        $max_players		= (!empty($max_players)) ? $max_players : '---';
		
		if($hostname == "---" OR $mapname == "---" OR $max_players == "---"){
		$update_query		= mysqli_query($db,"UPDATE servers SET online='1', num_players='$num_players', last_update='$last_update' WHERE id='$server_id'");
		} else {
		$update_query		= mysqli_query($db,"UPDATE servers SET online='1', hostname='$hostname', mapname='$mapname', num_players='$num_players', max_players='$max_players', last_update='$last_update' WHERE id='$server_id'");
		}
		
		
		//$del_player_query	= mysqli_query($db,"DELETE FROM players WHERE sid='$server_id'");
		foreach ($players as $player) {
			$player_nickname		= mysqli_real_escape_string($db,$player['gq_name']);
			$player_score			= mysqli_real_escape_string($db,$player['gq_score']);
			$player_time			= mysqli_real_escape_string($db,$player['time']);
			$player_nickname		= (!empty($player_nickname)) ? $player_nickname : 'anonymous';
			
            $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')  nickname='$player_nickname'");   

            } else {
                 $insert_player_query   = mysqli_query($db,"INSERT INTO players (id,nickname,score,time_online,mapname,sid) VALUES ('','$player_nickname','$player_score','$player_time','$mapname','$server_id')");   
            }
		}
	} else {
		$update_query		= mysqli_query($db,"UPDATE servers SET online='0',num_players='0' WHERE id='$server_id'");
		//$del_player_query	= mysqli_query($db,"DELETE FROM players WHERE sid='$server_id'");
	}
	
}

}
?>
MarPlo Mesaje:4343
Salut,
Ai o gresala in codul sql, la Update valoarea $player_nickname nu e pusa intre ghilimele simple (lipseste o ghilimea).

Poti sa folosesti direct formula INSERT ON DUPLICATE KEY UPDATE, astfel nu mai trebuie sa faci Select ca sa vezi daca numele e deja.
Cam asa (gasesti si pe internet exemple cu aceasta instructiune sql).

Cod: Selectaţi tot

mysqli_query($db,"INSERT INTO players (id,nickname,score,time_online,mapname,sid) VALUES ('','$player_nickname','$player_score','$player_time','$mapname','$server_id') 
 ON DUPLICATE KEY UPDATE score='$player_score', time_online='$player_time', mapname='$mapname', sid='$server_id'");
- In acest caz, coloana care nu trebuie duplicata e important sa fie de tip UNIQUE KEY (la tine e coloana "nickname").
mrdons Mesaje:2
Am incercat codul facut de tine si la fel imi duplica rezultaul in baza de date.
Eu am mai facut si Uniq Id, dar la interogare nu imi dau seama cum sa fac sa obtin Id-ul din baza de date a jucatorului (WHERE id='$id').

Cod: Selectaţi tot

$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')");   
            }
MarPlo Mesaje:4343
Cand se creaza tabelul in baza de date, coloana nickname trebuie sa fie de tip "UNIQUE KEY".
Iar id-ul ar trebui sa fie o valoare care sa nu o mai modifici la update, de obicei se creaza avand atributele "INT UNSIGNED UNIQUE PRIMARY KEY", astfel id-ul se adauga singur, automat la Insert; si il preiei din baza de date cu un Select.

Depaneaza acea parte de cod ca sa-ti dai seama daca sunt erori si unde ar putea fi problema; in logica codului sau in date.
Cam asa:

Cod: Selectaţi tot

$res = $db->query("SELECT * FROM players WHERE nickname='$player_nickname'");

if ($res->num_rows() > 0){
  //Vezi datele selectate
  while($row = $res->fetch_assoc()){
    $id = $row['id'];
    echo '<br> id: '. $id .' - name: '. $row['nickname']. ' - sid: '. $row['sid'];
  }
 
  $sql ="UPDATE players SET score='$player_score', time_online='$player_time', mapname='$mapname', sid='$server_id' WHERE id=$id";

} else {
  $sql ="INSERT INTO players (nickname,score,time_online,mapname,sid) VALUES ('$player_nickname','$player_score','$player_time','$mapname','$server_id')";
}

  // executa interogarea si verifica pentru erori
  if(!$db->query($sql)) echo '<br>Error: '. $db->error;

  echo '<br>'. $sql;  //Vezi ce date contine sql-ul transmis