How to solve duplicate entry?

[Hecter]

I’m pulling stats down from an external source but the external source has changed how it handles things which is causing me problems, everything else works but for 1 section of the database which is giving me duplicate entries, as nothing is being pulled the database enters the value of 0 so the next field would also have a value of 0, I tried auto increment but I still end up with a duplicate entry.

– phpMyAdmin SQL Dump
– version 3.4.11.1deb2+deb7u6
– http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Oct 27, 2016 at 02:44 AM
– Server version: 5.5.52
– PHP Version: 5.6.26-1~dotdeb+zts+7.1

SET SQL_MODE=“NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 */;

–
– Database: forum

---

–
– Table structure for table xf_yfl_rival_stats

CREATE TABLE IF NOT EXISTS xf_yfl_rival_stats (
match_id bigint(30) NOT NULL,
player_ea_id bigint(60) NOT NULL,
persona_name varchar(50) NOT NULL,
club_id bigint(20) NOT NULL,
assists int(2) NOT NULL,
cleansheetsany int(2) NOT NULL,
cleansheetsdef int(2) NOT NULL,
cleansheetsgk int(2) NOT NULL,
goals int(2) NOT NULL,
goalsconceded int(2) NOT NULL,
mom int(1) NOT NULL,
passattempts int(3) NOT NULL,
passesmade int(3) NOT NULL,
pos int(3) NOT NULL,
rating double(4,2) NOT NULL,
redcards int(2) NOT NULL,
saves int(3) NOT NULL,
shots int(3) NOT NULL,
tackleattempts int(3) NOT NULL,
tacklesmade int(3) NOT NULL,
yellowcards int(2) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (player_ea_id),
UNIQUE KEY match_id (match_id,player_ea_id),
KEY club_id (club_id),
KEY player_ea_id (player_ea_id),
KEY match_id_2 (match_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I’m getting the duplicate entry for the field player_ea_id is there any possible way to bypass this?

My understanding of mySQL isn’t the greatest, for example I pull down the match_id or persona_name and both of these fields have the same entry for the players however the player_ea_id is supposed to be unique, how do I get around this?

[astonecipher]

Why are you setting the player id as the primary key?

[php]PRIMARY KEY (player_ea_id),[/php]

That would mean that the player Id could no longer be used in that table.

[Hecter]

Why are you setting the player id as the primary key?

[php]PRIMARY KEY (player_ea_id),[/php]

That would mean that the player Id could no longer be used in that table.

Yeah I removed that, it was done on error before I exported.

[astonecipher]

Are there any fields in the table now that should not be duplicated? Like the match ID? Would there be more than one for that field?

[Hecter]

The match ID would duplicate for 22 players on each team, and then the club ID would duplicate for 11 people one each team, the only part that should be unqiue if the playerID but EA changed how this is handled and thats where I’ve run into problems. I made a post in the general help about it: http://www.phphelp.com/forum/general-php-help/major-problems-with-pulling-info-from-external-source/

But as a work around solution I decided to try and do it via the database which is not ideal.

It seems though that I have now solved the database issue by making in auto increment and dropping the unique from the match ID.