NeverUsedID
Well-Known Member
Hi, iam very New to MySQL.
I want to enhance pwn's privateserver SQL dB with stats for Overall zombikills for example.
I have a dB character_data with column killsz where the zombieskills per survivor are stored and a dB player_data where i have killZTotal.
If a character is killed the column alive get the value 0. i can copy this survivors to another dB First. So i dont Need the alivecheck, but its nice to know anyway
NOW i want to count all killsz for survivors that have alive=0 and Group them by playeruid. After that i want to update player_data column killsztotal with this value where playeruid is the Same in both tables. As playeruid is not the primarykey i dont NOW how to do this.
Maybe there is a much better Way to get the totalnumbers of zombiekills.
If you have any examples It would be Great. I only want to use MySQL no php.
Thank you very much for some examples. I googled around and found many examples, but in all examples i have to know the playeruid.
now i need to update player_data with this result. I now how to insert, but not how to update the playeruid that are already there.
EDIT: Solved it this way. If some of the pros knows a better way let me know
I want to enhance pwn's privateserver SQL dB with stats for Overall zombikills for example.
I have a dB character_data with column killsz where the zombieskills per survivor are stored and a dB player_data where i have killZTotal.
If a character is killed the column alive get the value 0. i can copy this survivors to another dB First. So i dont Need the alivecheck, but its nice to know anyway
NOW i want to count all killsz for survivors that have alive=0 and Group them by playeruid. After that i want to update player_data column killsztotal with this value where playeruid is the Same in both tables. As playeruid is not the primarykey i dont NOW how to do this.
Maybe there is a much better Way to get the totalnumbers of zombiekills.
If you have any examples It would be Great. I only want to use MySQL no php.
Thank you very much for some examples. I googled around and found many examples, but in all examples i have to know the playeruid.
now i need to update player_data with this result. I now how to insert, but not how to update the playeruid that are already there.
EDIT: Solved it this way. If some of the pros knows a better way let me know
Code:
CREATE TEMPORARY TABLE if not exists `countTotals` (
`playerUID` VARCHAR(45) NOT NULL DEFAULT '0',
`KillsBTotal` INT(11) NOT NULL DEFAULT '0',
`KillsHTotal` INT(11) NOT NULL DEFAULT '0',
`KillsZTotal` INT(11) NOT NULL DEFAULT '0',
`HumanityTotal` INT(11) NOT NULL DEFAULT '0',
`distanceFootTotal` INT(11) NOT NULL DEFAULT '0',
`HeadshotsZTotal` INT(11) NOT NULL DEFAULT '0',
INDEX `playerUID` (`playerUID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
INSERT INTO countTotals (playerUID, KillsBTotal, KillsHTotal, KillsZTotal, HumanityTotal, distanceFootTotal, HeadshotsZTotal ) SELECT PlayerUID, sum(killsB), sum(killsH), sum(killsZ), sum(Humanity), sum(distanceFoot), sum(HeadshotsZ) FROM character_dead GROUP BY PlayerUID;
UPDATE player_data pd, counttotals ct
SET pd.killszTotal = ct.KillsZTotal, pd.killsHTotal = ct.KillsHTotal, pd.killsBTotal = ct.KillsBTotal, pd.HumanityTotal = ct.HumanityTotal, pd.distanceFootTotal = ct.distanceFootTotal, pd.HeadshotsZTotal = ct.HeadshotsZTotal
WHERE ct.PlayerUID = pd.PlayerUID;
DROP TEMPORARY TABLE countTotals;