Mysql php table shows duplicates

Question

I just started a game server and made a simple leaderboard on my website where it shows players stats. The problem I'm facing is that it will show duplicate rows in the table.

Here's what I mean:

And here is my code.

<?php        
mysql_connect('','','') or die(mysql_error());
mysql_select_db('dbname')  or die(mysql_error());
$query=mysql_query("select * from playerstats,playerinfo limit 0,10")  or die(mysql_error());
echo'<table border="1" ><th >NOM DU JOEUR</th><th>VICTIMES</th><th>AI TUER</th><th>TEAMKILLS</th><th>DECES</th><th>RÉANIMATIONS</th><th>ARGENT</th>';
while($res=mysql_fetch_array($query))
{
  echo'<tr><td>'.$res['Name'].'</td><td>'.$res['PlayerKills'].'</td><td>'.$res['AIKills'].'</td><td>'.$res['TeamKills'].'</td><td>'.$res['DeathCount'].'</td><td>'.$res['ReviveCount'].'</td><td>'.$res['BankMoney'].'</td></tr>';
}
echo'</table>';
?>

I would apreciate some help. I don't want duplicate entries if the username is already listed.

EDIT: Here is the 2 tables i am trying to fetch from

Here is the 2 tables i am trying to fetch from

-- -----------------------------------------------------
-- Table `PlayerStats`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PlayerStats` (
  `PlayerUID` VARCHAR(32) NOT NULL,
  `LastModified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `PlayerKills` INT UNSIGNED NOT NULL DEFAULT 0,
  `AIKills` INT UNSIGNED NOT NULL DEFAULT 0,
  `TeamKills` INT UNSIGNED NOT NULL DEFAULT 0,
  `DeathCount` INT UNSIGNED NOT NULL DEFAULT 0,
  `ReviveCount` INT UNSIGNED NOT NULL DEFAULT 0,
  `CaptureCount` INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE INDEX `idx_PlayerStats_uniquePlayer` (`PlayerUID` ASC),
  CONSTRAINT `fk_PlayerStats_PlayerInfo`
    FOREIGN KEY (`PlayerUID`)
    REFERENCES `PlayerInfo` (`UID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `PlayerInfo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PlayerInfo` (
  `UID` VARCHAR(32) NOT NULL,
  `CreationDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `Name` VARCHAR(256) CHARACTER SET 'utf8' NULL,
  `LastSide` VARCHAR(32) NULL,
  `BankMoney` FLOAT NOT NULL DEFAULT 0,
  `BattlEyeGUID` VARCHAR(32) NULL,
  PRIMARY KEY (`UID`))
ENGINE = InnoDB;

Professor Abronsius · Accepted Answer · 2015-12-06 08:22:01Z

1

I think you want to join the tables rather than select from both as you are. try:

select * from `playerinfo` i
left outer join `playerstats` s on s.`PlayerUID`=i.`UID`
limit 0,10;

answered Dec 6, 2015 at 8:22

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikolay Yenbakhtov · Accepted Answer · 2015-12-06 07:59:26Z

1

I think your problem at SQL query, you get all from two tables, that's why it dublicate:

"select * from playerstats,playerinfo limit 0,10"

try this one: "select * from playerinfo limit 0,10"

answered Dec 6, 2015 at 7:59

Nikolay Yenbakhtov

4434 silver badges14 bronze badges

Comments

Waqar Haider · Accepted Answer · 2015-12-06 07:57:11Z

0

you can select DISTINCT values from database,

$query=mysql_query("select DISTINCT name from playerstats,playerinfo limit 0,10")  or die(mysql_error());

answered Dec 6, 2015 at 7:57

Waqar Haider

9731 gold badge11 silver badges35 bronze badges

4 Comments

t0ks1ck514 Over a year ago

heres the 2 tables im trying to fetch data from

Waqar Haider Over a year ago

you should add relationships between the table and than use joins to get the data.

t0ks1ck514 Over a year ago

If I fetch playerUID it will show the player uid wich is not the playername its numbers like (8303979326389)

Waqar Haider Over a year ago

will add all other fields in select DISTINCT playerUID,player Kills,etc and use joins

Santy · Accepted Answer · 2015-12-06 07:59:35Z

0

You seem to have different results in that scoreboard image you showed. Are they separate games played? How do you put them in your database? New game = adding a new row? If there are multiple database entries with the same nicks, then obviously your code there will output them all.

answered Dec 6, 2015 at 7:59

Santy

3872 silver badges7 bronze badges

1 Comment

t0ks1ck514 Over a year ago

2 colums from 1 table and the others for the other table since the way the game database is made is separate tables

t0ks1ck514 · Accepted Answer · 2015-12-06 08:09:02Z

0

Here is the 2 tables i am trying to fetch from

-- -----------------------------------------------------
-- Table `PlayerStats`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PlayerStats` (
  `PlayerUID` VARCHAR(32) NOT NULL,
  `LastModified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `PlayerKills` INT UNSIGNED NOT NULL DEFAULT 0,
  `AIKills` INT UNSIGNED NOT NULL DEFAULT 0,
  `TeamKills` INT UNSIGNED NOT NULL DEFAULT 0,
  `DeathCount` INT UNSIGNED NOT NULL DEFAULT 0,
  `ReviveCount` INT UNSIGNED NOT NULL DEFAULT 0,
  `CaptureCount` INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE INDEX `idx_PlayerStats_uniquePlayer` (`PlayerUID` ASC),
  CONSTRAINT `fk_PlayerStats_PlayerInfo`
    FOREIGN KEY (`PlayerUID`)
    REFERENCES `PlayerInfo` (`UID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `PlayerInfo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PlayerInfo` (
  `UID` VARCHAR(32) NOT NULL,
  `CreationDate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `Name` VARCHAR(256) CHARACTER SET 'utf8' NULL,
  `LastSide` VARCHAR(32) NULL,
  `BankMoney` FLOAT NOT NULL DEFAULT 0,
  `BattlEyeGUID` VARCHAR(32) NULL,
  PRIMARY KEY (`UID`))
ENGINE = InnoDB;

answered Dec 6, 2015 at 8:09

t0ks1ck514

236 bronze badges

1 Comment

Adam Michalik Over a year ago

Please put it in the original post (use the "edit" option) instead of submitting it as an answer.

Collectives™ on Stack Overflow

Mysql php table shows duplicates

5 Answers 5

Comments

Comments

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related