Adding two counts together and grouping by a different field

NiallThistle

Here is some code I have which is designed to group by the teams that a player has made appearances against ->

        $get_player_totals = mysql_query("
	SELECT M.MatchOpponent AS opponent,
	COUNT(A.AppearancePlayerID) +
	( SELECT COUNT(S.SubstitutionPlayerIDIn)
	FROM tplss_substitutions S, tplss_matches M
	WHERE S.SubstitutionPlayerIDIn = A.AppearancePlayerID
	AND S.SubstitutionPlayerIDIn = '$id'
	AND S.SubstitutionMatchID = M.MatchID) AS total_apps
	FROM tplss_appearances A, tplss_matches M
	WHERE A.AppearanceMatchID = M.MatchID AND
	A.AppearancePlayerID = '$id'
	GROUP BY M.MatchOpponent
	ORDER BY total_apps DESC
	LIMIT 5
	", $connection) or die(mysql_error());

Can anyone please suggest to me why this example is different any other time I've used this query, and why it is not working? It displays a result, but the result is not correct and is always about 300% more than it should be.

A working example, which shows total_apps figure between two dates ->

$get_player_totals = mysql_query("
SELECT A.AppearancePlayerID AS playerid,
COUNT(A.AppearancePlayerID) +
( SELECT COUNT(S.SubstitutionPlayerIDIn)
FROM tplss_substitutions S, tplss_matches M
WHERE S.SubstitutionPlayerIDIn = A.AppearancePlayerID
AND S.SubstitutionMatchID = MatchID AND 
M.MatchDateTime > '19991231' AND
M.MatchDateTime < '20100101') AS total_apps
FROM tplss_appearances A, tplss_matches M
WHERE A.AppearanceMatchID = MatchID AND
M.MatchDateTime > '19991231' AND
M.MatchDateTime < '20100101'
GROUP BY A.AppearancePlayerID
ORDER BY total_apps DESC
LIMIT 5
", $connection) or die(mysql_error());

NiallThistle

I think I have worked out why it's happening, but not sure how to modify it to resolve it.

The system seems to be counting the A.AppearancePlayerID figure correctly, and grouping it by the opponent. So, say for instance John has played 5 games against Team A, it will count that 5 accurately. Whereas instead of counting the SubstitutionPlayerIDIn for ONLY the grouped opponent, it counts ALL the SubstitutionPlayerIDIn instances for that player. So in other words we end up with Appearances vs the grouped team + all Substitutions vs all teams. When basically I want Appearances vs the grouped team + Substitutions vs the grouped team.

Complicated, but I know what its adding at least. Just don't know how to modify the query to tell it to group both!

johanafm

Always indent your code (SQL is also code, just not PHP-code) if you want to be able to see what it's doing. Then it would look something like

	SELECT M.MatchOpponent AS opponent,
		COUNT(A.AppearancePlayerID) +
		(
			SELECT COUNT(S.SubstitutionPlayerIDIn)
			FROM tplss_substitutions S, tplss_matches M
			WHERE S.SubstitutionPlayerIDIn = A.AppearancePlayerID
			AND S.SubstitutionPlayerIDIn = '$id'
			AND S.SubstitutionMatchID = M.MatchID
		) AS total_apps
	FROM tplss_appearances A, tplss_matches M
	WHERE A.AppearanceMatchID = M.MatchID AND
	A.AppearancePlayerID = '$id'
	GROUP BY M.MatchOpponent
	ORDER BY total_apps DESC
	LIMIT 5

and once you have made it readable through indentation, you can reduced it to its relevant parts

	SELECT M.MatchOpponent AS opponent,
		COUNT(A.AppearancePlayerID) +
		(
			SELECT COUNT(S.SubstitutionPlayerIDIn)

which tells you that for every single row being selected, you will SELECT COUNT(S.SubstitutionPlayerIDIn) which means a whole lot of counting...

Are you able to write TWO queries where one counts appearances and one counts subsittutions?

NiallThistle

I notice before your edit, you mentioned something about not using "or die"? What is the reason for that?

johanafm

Ah yes, the edit was due to me incorrectly seeing a parse error at first, and right after posting realizing there was no such thing.

The comment on or die(mysql_error()) being bad is due to the fact that as soon as an error occurs, the end user gets to see something like
"There was an error near "FROM 'Either pointless to users or possibly useful to hackers'". Leave users in the dark concerning your syntax errors, PHP version, DBMS and DBMS version. Do let them know that there was an error in a generic way "There was an error with the database. Please try again or contact [support link here]".

Did you get it to work by splitting the problem up in two before recombining?

NiallThistle

johanafm;11019651 wrote:
Ah yes, the edit was due to me incorrectly seeing a parse error at first, and right after posting realizing there was no such thing.

The comment on or die(mysql_error()) being bad is due to the fact that as soon as an error occurs, the end user gets to see something like
"There was an error near "FROM 'Either pointless to users or possibly useful to hackers'". Leave users in the dark concerning your syntax errors, PHP version, DBMS and DBMS version. Do let them know that there was an error in a generic way "There was an error with the database. Please try again or contact [support link here]".

So, in that case, would you instead structure that as

or die("Error! Please contact support.")

For example?

NiallThistle

johanafm;11019173 wrote:
Are you able to write TWO queries where one counts appearances and one counts subsittutions?

Yes. I can then add the results together for a player, but I'm not sure then how I would sort by opponents. Ultimately I want this to be a list which shows the teams a player has most frequently scored against.

johanafm

NiallThistle;11019753 wrote:
So, in that case, would you instead structure that as
or die("Error! Please contact support.")

Well, considering that adds no error info to the person who is debugging, no.

if (!mysqli_query())
{
	# Most definitely use a contruct such as this one, since it will then
	# log / display error according to php.ini settings, usually
	# log & display: development server,
	# log only: test (if you have second staging area) and production servers
	trigger_error(mysqli_error());

# This is how I prefer showing errors to users
# Although it can of course contain a little additional info, such as
# "A search error..." or whatever is relevant.
$errors[] = 'An error has occured. Please contact support if problem remains.';

# but you could of course go with die() if you really only want a blank page with an error.
# I prefer showing the users the exact same interface they have otherwise, except with
# an error message in the content part of the page	
}
# ... since I don't use die()
else
{

}

johanafm

NiallThistle;11019755 wrote:
Ultimately I want this to be a list which shows the teams a player has most frequently scored against.

Well, what do you have so far? And perhaps I should add that I never ever watch sports, so don't assume I'd know where a specific field would go (or even that a specific field even exists).

But I'm guessing what you ask for is not possible in a query dealing with the above things. Counting appearances and substitutions per player is one thing, while listing which teams they scored against is another. While you have both informtion parts initially, when you group on player to count there appearances and substitions, you lose against which teams they played (they certainly didn't play 20 times vs one team last season...).

So, if I'm correct, there would be two ways of doing this. Either you do the appearances part using count and group by, and then do another query to get the latest goal info. Or you could skip the group by part in the previous query and instead do that counting using a loop in php, and at the same time also check which is the latest date they scored a goal and against what team that was.

johanafm

johanafm;11019893 wrote:
So, if I'm correct

A third option occurred to me. If you have info on the player scoring alongside the rest, then you could run MAX on the game date when grouping. After that, you'd still need a subsequent query, but finding out which team a player went up against on a particular date should be quick.