Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Posts
    7

    Question Unanswered: Query Help Needed

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What links your goal(s) to a specific season?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2005
    Posts
    7
    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?
    Last edited by squire; 10-03-07 at 10:55.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Jan 2005
    Posts
    7

    Question

    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.

  6. #6
    Join Date
    Oct 2007
    Posts
    3

    Wink

    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

  7. #7
    Join Date
    Mar 2004
    Posts
    480
    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.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    Last edited by healdem; 10-03-07 at 15:39.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jan 2005
    Posts
    7
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •