Multiple counts() in one query

[fritz.fx]

Hey guys.

I haven't done any PHP for a while and my memory isn't so good 

I'm trying to do a count from 2 columns in one table from an array of names.

Table setup:

 

this is what I have:

$names = array('Losos', '.minion', 'STAVY', 'brothrsinarms', '.-(AUST)-.Fatal', 'Vergetta');
foreach ($names as $names) {
$result = mysql_query("SELECT *, count(killer) as kills, count(victim) as deaths FROM tbl_killlog where killer = '$names'");
   
while($row = mysql_fetch_array($result))
{
  $name = $row['killer'];
  $kills = $row['kills'];
  $deaths = $row['deaths'];
  echo "$name == $kills == $deaths<br>";
  }
}

 

So, what I want, is for each $name get the number of "kills" (killer) AND the number of "deaths" (victim) but for some reason I'm getting the same result from both.

Anybody got any ideas??

 

Oh, also, just one more query, Another table I have is a log of names/countries. what would  the query be for counting individual country names.. eg " ireland = 5, Germany = 15 etc etc  ??

table pic:

 

Cheers

Fritz

 

[DavidAM]

1) Why would you expect to get a different count for killers than for deaths? Can you have a killer without a death? or a death without a killer? 

 

I don't see how that code could be working at all:

foreach ($names as $names)

would seem to wipe out your array since you are using the array's variable name to hold each iteration.

 

you could do this in a single query using something like this:

$names = array('Losos', '.minion', 'STAVY', 'brothrsinarms', '.-(AUST)-.Fatal', 'Vergetta');

$namesIn = "'" . implode("','", $names) . "'";
$sql = "SELECT killer, count(killer) as kills, count(victim) as deaths 
FROM tbl_killlog 
WHERE killer IN ($namesIn)

while($row = mysql_fetch_array($result))
{
  $name = $row['killer'];
  $kills = $row['kills'];
  $deaths = $row['deaths'];
  echo "$name == $kills == $deaths<br>";
  }
}

but again, the counts will be the same. If there is something about the data that should change the count, let us know and we can help you figure it out.

 

2) Look at GROUP BY

SELECT logCountry, COUNT(*) AS logs FROM tablename GROUP BY logCountry

[fritz.fx]

I'm getting nothing out of your query. (sorry, I get a bit slow without sleep) 

1) Why would you expect to get a different count for killers than for deaths? Can you have a killer without a death? or a death without a killer? 

I expect different results this way. eg: george kills fred 5 times .... fred kills george 15 times. result should be

george == 5 == 15

fred ==15 ==5

 

haven't tried out your country query yet but it looks like it's exactly what I'm looking for.

[fritz.fx]

OK, can't edit my post, I think I found my problem, let me get some sleep and I'll give this another hit.

[DavidAM]

Oops, I left something important out of my original suggestion:

$sql = "SELECT killer, count(killer) as kills, count(victim) as deaths 
FROM tbl_killlog 
WHERE killer IN ($namesIn)
GROUP BY killer";

but that is not going to give you what you are looking for.

 

To get the kill and death counts going both ways, I think we have to do a couple of psuedo tables.  I haven't done this much (and they were not supported in earlier versions of mysql) but it would look something like this:

SELECT K.killer, K.victim, K.kills, V.deaths
FROM (SELECT killer, victim, count(*) AS kills FROM tbl_killlog WHERE killer = '$kName' GROUP BY victim) AS K
OUTER JOIN 
(SELECT killer, victim, count(*) as deaths FROM tbl_killlog WHERE victim = '$kName' GROUP BY killer) AS V
ON k.victim = v.killer

I think that will do it.  The resultset would be one row for each of the killer's victims:

killer => the killer you are looking at

victim => a victim of the killer

kills  => the number of times killer killed victim

deaths => the number of times victim killed killer

 

The OUTER JOIN should make sure we get those cases where victim never has killed killer (and so deaths will be zero).

 

This will work easier in your original loop style (just change the foreach a little):

$names = array('Losos', '.minion', 'STAVY', 'brothrsinarms', '.-(AUST)-.Fatal', 'Vergetta');
foreach ($names as $kName) { // Do not use the same variable in the AS part
$result = mysql_query("SELECT K.killer, K.victim, K.kills, V.deaths
FROM (SELECT killer, victim, count(*) AS kills FROM tbl_killlog WHERE killer = '$kName' GROUP BY victim) AS K
OUTER JOIN 
(SELECT killer, victim, count(*) as deaths FROM tbl_killlog WHERE victim = '$kName' GROUP BY killer) AS V
ON k.victim = v.killer");

while($row = mysql_fetch_array($result)) {
	/* you don't have to assign to intermediate variables here
	   you can use the $row array in your echo if you want */
	$name = $row['killer'];
	$victim = $row['victim'];
	$kills = $row['kills'];
	$deaths = $row['deaths'];

	echo "$name -> $victim == $kills == $deaths<br>";
}
}