Query Help Needed

squire · #1 (**permalink**) 10-03-07, 09:20

Hi,

I have a DB for my football (soccer) club where I am trying to get the top goal scorers for this season.

The tables involved are:

players (player_id, fname, lname, ...)
season (season_id, season, ...)
fixtures (fixture_id, ...)
goals (goal_id, fixture_id, player_id, ...)

I need to count the amount of goals an individual player (player_id) has scored via counting the number of times the unique player_id appears in the goals table. I also need to ensure that the goals counted are only for this season (season_id = 2). I need to run through this for all players and then list them in order from the highest amount of goals scored to the lowest.

This might be easier if the goals table had the season_id as a field but I think this would duplicate data.

Do I need to perform a type of outer/inner join? I am not to clued up on outer/inner joins and need advice. I am looking at a MySql book about joins but I am still unclear. If you can help or point me in the right direction, it would be most helpful.

Thank you for taking the time to read.

gvee · #2 (**permalink**) 10-03-07, 09:35

What links your goal(s) to a specific season?

squire · #3 (**permalink**) 10-03-07, 09:52

Hi George,

the goals are not linked to a specific season (which may be the problem). A player goal is linked to the fixture_id of the fixtures table. The fixture is then is linked to the season via the season_id field on the fixtures table. It wouold be easy enough for me to add in a field where the season_id is added to the goals table but would that not duplicate data?

Pat Phelan · #4 (**permalink**) 10-03-07, 10:20

Would this work?

Code:

SELECT Count(*), goals.player_id
   FROM fixtures
   INNER JOIN goals
      ON (goals.fixture_id = fixtures.fixture_id)
   WHERE  2 = fixtures.season_id
   ORDER BY Count(*) DESC, goals.player_id

-PatP

squire · #5 (**permalink**) 10-03-07, 12:37

Pat, I tried this but I don't think it was bringing back any results.

I have a php while loop and when I use the mysql_fetch_array function, it gives an error saying: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource....

Maybe I need to use a different method of displaying the result or maybe I need to use the GROUP BY feature?

Pat, you have this piece in your SQL:

2 = fixtures.season_id

Should this be reversed as such: fixtures.season_id = 2?

I also need to pull the player fname and lname from the players table so I would assume I'd need another clause such as goals.player_id = players.player_id.

Thanks for your time.

dhanuxz · #6 (**permalink**) 10-03-07, 13:45

Try this...

First you need to add the seasonid on the fixtures table
then try this query

Code:

SELECT
	players.playerid,
	players.name,
	count(goals.playerid) as playersgoal
FROM
	goals
	INNER JOIN
	players
		ON
		players.playerid = goals.playerid
	INNER JOIN
	fixtures
		ON
		fixtures.fixtureid = goals.fixtureid
	INNER JOIN
	seasons
		ON
		seasons.seasonid = fixtures.seasonid
WHERE
	seasons.seasonid = 2
GROUP BY
	players.playerid,
	players.name
ORDER BY
	count(goals.playerid) DESC

guelphdad · #7 (**permalink**) 10-03-07, 13:58

Quote:

Originally Posted by squire

Pat, I tried this but I don't think it was bringing back any results.

I have a php while loop and when I use the mysql_fetch_array function, it gives an error saying: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource....

What you need to do is trouble shoot your mysql directly in mysql, not in any php code. if you are using phpmyadmin use the SQL tab and try any sql there first BEFORE doing anything else with it in your code. you can then be sure that any errors after that have been introduced in your php code and not the sql itself.

healdem · #8 (**permalink**) 10-03-07, 14:31

did you examine the error code for the preceeding MYSQL statements in PHP: mysql_errno() or mysql_error()?
did you take advantage of the "or die" construct in PHP to stop execution on error? commonly used as

Code:

$ResultSet = @mysql_query($strSQL,$Connection) or die (Echo "MySQL bailed out on me citing error number:".mysql_errno()."; apparently the feeble excuse offered is: ".mysql_error()."<BR>The SQL was:$strSQL);

did you set your error_reporting level to something sensible for development purposes
are you certain you don't have a typo, that the in your MYSQL resource identifier
should this question be asked in the PHP section now we know you are using MySQL in PHP

there is no doubt that testing your SQL in either MySQL Query Browser of PHP Admin may make sense. If you have a local MySQL server use the MySQL Query browser (and whilst you are at it download the MySQL Adminsitrator), if you are using a remote MySQL db then you probably have to use PHPMyAdmin.. which is OK if a lot less helpfull than the Query Browser.

squire · #9 (**permalink**) 10-03-07, 19:16

Quote:

Originally Posted by dhanuxz

Try this...

First you need to add the seasonid on the fixtures table
then try this query

Code:

SELECT
	players.playerid,
	players.name,
	count(goals.playerid) as playersgoal
FROM
	goals
	INNER JOIN
	players
		ON
		players.playerid = goals.playerid
	INNER JOIN
	fixtures
		ON
		fixtures.fixtureid = goals.fixtureid
	INNER JOIN
	seasons
		ON
		seasons.seasonid = fixtures.seasonid
WHERE
	seasons.seasonid = 2
GROUP BY
	players.playerid,
	players.name
ORDER BY
	count(goals.playerid) DESC

dhanuxz, that worked an absolute treat - thank you very much.

guelphdad/healdem, I used phpmyadmin to execute the SQL - thats a very good tip that I really should have thought of and it is far easier to identify problems!

This example should come in very handy for other stuff I need to do that is similar.

Once again, thank you to everyone for reading and for your help. It has been brilliant!

Internet Services

Web Development

Digital Marketing

Consumer Tech