hi looking for a bit of advice and possible help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sinus
Forum Newbie
Posts: 10
Joined: Fri Nov 22, 2002 9:46 pm

hi looking for a bit of advice and possible help

Post by sinus »

I am making a website that needs to pull alot of stats for individuals from various tables within a database then do a few calculations on data pulled from the database, the idea is that the site will run similair to a fantasy football site where players get points for wins, goals scored, assissts etc. But rather than just update the player info from one table, and overwrite the previous info in it each time it is updated, I'm trying to store all information from each match played and do the calculations when the page is called to display players stats.
below are the queries i need to execute to get the required information that i need. At present i plan to excute each query and pass the results on to the next query to try a get the correct output. (i'm not great at sql) i'm using php. I would like to know if there's an easier why to approach this and if so how.

Code: Select all

Get games a team played this season:
SELECT r.iPoints AS 'Points', f.sName AS 'Fixture', f.fixtureID AS 
'FixtureID', t1.sName AS 'Team_Home', t2.sName AS 'Team_Away', 
r.iGoalsConceded AS 'GoalsAgainst' FROM
fixture f INNER JOIN result r ON (f.fixtureID = r.iFixtureID) INNER JOIN 
team t1 ON (f.iHomeTeamID = t1.TeamID) INNER JOIN team t2 ON (f.iAwayTeamID 
= t2.TeamID)
WHERE f.iSeasonID = 1 AND f.iDivisionID = 1 AND r.iTeamID = 1 AND 
(f.iHomeTeamID = 1 OR f.iAwayTeamID = 1)

Get list of team players who played in games that team won:
SELECT p.playerID, p.sName, COUNT(ps.iFixtureID) AS 'GamesWon', 
COUNT(ps.iFixtureID) * 3 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2)
GROUP BY p.sName

Get list of team players who played in games that team drew:
SELECT p.playerID, p.sName, COUNT(ps.iFixtureID) AS 'GamesDrawn', 
COUNT(ps.iFixtureID) * 1 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2)
GROUP BY p.sName

Get list of team players who scored goals in games this season:
SELECT p.playerID, p.sName, SUM(ps.iGoalsScored) AS 'GoalsScored', 
SUM(ps.iGoalsScored) * 3 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.iGoalsScored <> 0
GROUP BY p.sName

Get list of team players who registered goal assists in games this season:
SELECT p.playerID, p.sName, SUM(ps.iGoalAssist) AS 'GoalAssists', 
SUM(ps.iGoalAssist) * 2 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.iGoalAssist <> 0
GROUP BY p.sName

Get list of team players who got man of the match in games this season:
SELECT p.playerID, p.sName, SUM(ps.bManOfMatch) AS 'ManofMatch', 
SUM(ps.bManOfMatch) * 5 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.bManOfMatch <> 0
GROUP BY p.sName

Get list of team players who got yellow cards in games this season:
SELECT p.playerID, p.sName, SUM(ps.iYellowCard) AS 'YellowCards', 
SUM(ps.iYellowCard) * -2 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.iYellowCard <> 0
GROUP BY p.sName

Get list of team players who got red cards in games this season:
SELECT p.playerID, p.sName, SUM(ps.bRedCard) AS 'RedCards', SUM(ps.bRedCard) 
* -2 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1,2) AND ps.bRedCard <> 0
GROUP BY p.sName

Get list of fixtures this season that team kept a clean sheet:
CREATE TEMPORARY TABLE cleansheet SELECT f.fixtureID AS 'fixtureID' FROM
fixture f INNER JOIN result r ON (f.fixtureID = r.iFixtureID)
WHERE f.iSeasonID = 1 AND f.iDivisionID = 1 AND r.iTeamID = 1 AND 
r.iGoalsConceded = 0 AND (f.iHomeTeamID = 1 OR f.iAwayTeamID = 1)

Get list of team goalkeepers who kept cleansheets this season
SELECT p.playerID, p.sName, COUNT(ps.iFixtureID) AS 'KeeperCleansheets', 
COUNT(ps.iFixtureID) * 4 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1) AND ps.iPositionID = 1
GROUP BY p.sName

Get list of team defenders who kept cleansheets this season
SELECT p.playerID, p.sName, COUNT(ps.iFixtureID) AS 'DefenderCleansheets', 
COUNT(ps.iFixtureID) * 2 AS 'Points'
FROM playerstatistics ps INNER JOIN player p ON (ps.iPlayerID = p.playerID)
WHERE p.iTeamID = 1 AND ps.iFixtureID IN (1) AND ps.iPositionID = 2
GROUP BY p.sName
Any help and advice would be great.

thanks sinus
Post Reply