Making 2 Tables interact

[eaglesfan17]

Alright, I have a database with several tables that I draw queries from. I need to use 2 tables from that database for a particular operation that I'm trying to perform...

query1 =

$query = "SELECT team, wins, loses, ties
         FROM standings
         WHERE league_id='$league_id'
         AND season='$season'
         AND team IN ('$team1', '$team2')";

query2 =

$query = "SELECT team, sum( passing_cmp ) AS passing_cmp, sum( passing_att ) AS passing_att, sum( passing_yds ) AS passing_yds, sum( passing_td ) AS passing_td, sum( passing_int ) as passing_int
        FROM game_passing, game_summary
        WHERE game_summary.game_id = game_passing.game_id
        AND league_id =1
        AND season = 2006
        AND team IN ('$team1', '$team2')
        GROUP BY team";

Basically, since I don't have a "Total Games Played" entry in my database so to calculate games played i would need to do something like

$games_played = sum($wins + $loses + $ties)

from query 1.

NOW I want to calculate Passing_yds PER GAME. So I would need to say something like

$passing_yds_per_game = $passing_yds / $games_played

.

So what I'm asking is, how do I get these 2 tables to interact with one another?

[Roger_Ramjet]

Make lif simple for yourself. When you add the results for each game, update a games played field, ie calculate it once not every time you need to use it.

[eaglesfan17]

Sorry to hassle and not attempt something before asking, but my knowledge of SQL is extremely limited...Say I wanted to create a field named "games_played" in my "game_passing" table and for each game insterted have the field increase by 1 numerically, how would I do this?

[eaglesfan17]

If you would like to see the code in which is used to load gamestats, I can show you, but I must warn...it is 256 lines...

[Roger_Ramjet]

Better to post the table layouts - all of them. From the sound of it your schema needs reviewing. Get that right and the rest comes easy.

[eaglesfan17]

Okay....Here is my basic table dump....

-- phpMyAdmin SQL Dump
-- version 2.6.1-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Jul 11, 2005 at 09:27 PM
-- Server version: 4.1.10
-- PHP Version: 4.3.11

-- 
-- Table structure for table `game_blocking`
-- 

CREATE TABLE `game_blocking` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `team` tinytext NOT NULL,
  `blocking_player` tinytext NOT NULL,
  `blocking_pancakes` tinyint(4) NOT NULL default '0',
  `blocking_sacks` tinyint(4) NOT NULL default '0',
  `blocking_allowed` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_defense`
-- 

CREATE TABLE `game_defense` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `team` tinytext NOT NULL,
  `defense_player` tinytext NOT NULL,
  `defense_tot` tinyint(4) NOT NULL default '0',
  `defense_loss` tinyint(4) NOT NULL default '0',
  `defense_sack` tinyint(4) NOT NULL default '0',
  `defense_ff` tinyint(4) NOT NULL default '0',
  `defense_frec` tinyint(4) NOT NULL default '0',
  `defense_yds` int(11) NOT NULL default '0',
  `defense_td` tinyint(4) NOT NULL default '0',
  `defense_int` tinyint(4) NOT NULL default '0',
  `defense_ret` int(11) NOT NULL default '0',
  `defense_avg` float NOT NULL default '0',
  `defense_deflections` tinyint(4) NOT NULL default '0',
  `defense_safeties` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_kicking`
-- 

CREATE TABLE `game_kicking` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `team` tinytext NOT NULL,
  `kicking_player` tinytext NOT NULL,
  `kicking_fgm` tinyint(4) NOT NULL default '0',
  `kicking_fga` tinyint(4) NOT NULL default '0',
  `kicking_fgpct` float NOT NULL default '0',
  `kicking_fgsblocked` tinyint(4) NOT NULL default '0',
  `kicking_xpa` tinyint(4) NOT NULL default '0',
  `kicking_xpm` tinyint(4) NOT NULL default '0',
  `kicking_xppct` float NOT NULL default '0',
  `kicking_xpsblocked` tinyint(4) NOT NULL default '0',
  `kicking_kickoffs` tinyint(4) NOT NULL default '0',
  `kicking_touchbacks` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_kickreturns`
-- 

CREATE TABLE `game_kickreturns` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `team` tinytext NOT NULL,
  `kickreturns_player` tinytext NOT NULL,
  `kickreturns_att` tinyint(4) NOT NULL default '0',
  `kickreturns_yds` int(11) NOT NULL default '0',
  `kickreturns_avg` float NOT NULL default '0',
  `kickreturns_td` tinyint(4) NOT NULL default '0',
  `kickreturns_long` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_passing`
-- 

CREATE TABLE `game_passing` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `team` tinytext NOT NULL,
  `passing_player` tinytext NOT NULL,
  `passing_cmp` tinyint(4) NOT NULL default '0',
  `passing_att` tinyint(4) NOT NULL default '0',
  `passing_yds` int(11) NOT NULL default '0',
  `passing_pct` float NOT NULL default '0',
  `passing_ypa` float NOT NULL default '0',
  `passing_sack` tinyint(4) NOT NULL default '0',
  `passing_td` tinyint(4) NOT NULL default '0',
  `passing_int` tinyint(4) NOT NULL default '0',
  `passing_long` tinyint(4) NOT NULL default '0',
  `passing_rating` float NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_pog`
-- 

CREATE TABLE `game_pog` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `league_id` int(11) NOT NULL default '0',
  `season` mediumint(9) NOT NULL default '0',
  `week` tinyint(4) NOT NULL default '0',
  `player` tinytext NOT NULL,
  `team` tinytext NOT NULL,
  `stats` tinytext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_punting`
-- 

CREATE TABLE `game_punting` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `team` tinytext NOT NULL,
  `punting_player` tinytext NOT NULL,
  `punting_att` tinyint(4) NOT NULL default '0',
  `punting_yds` int(11) NOT NULL default '0',
  `punting_avg` float NOT NULL default '0',
  `punting_long` tinyint(4) NOT NULL default '0',
  `punting_blocks` tinyint(4) NOT NULL default '0',
  `punting_in20` tinyint(4) NOT NULL default '0',
  `punting_touchbacks` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_puntreturns`
-- 

CREATE TABLE `game_puntreturns` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `team` tinytext NOT NULL,
  `puntreturns_player` tinytext NOT NULL,
  `puntreturns_att` tinyint(4) NOT NULL default '0',
  `puntreturns_yds` int(11) NOT NULL default '0',
  `puntreturns_avg` float NOT NULL default '0',
  `puntreturns_long` tinyint(4) NOT NULL default '0',
  `puntreturns_td` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_receiving`
-- 

CREATE TABLE `game_receiving` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `team` tinytext NOT NULL,
  `receiving_player` tinytext NOT NULL,
  `receiving_rec` tinyint(4) NOT NULL default '0',
  `receiving_yds` int(11) NOT NULL default '0',
  `receiving_avg` float NOT NULL default '0',
  `receiving_long` tinyint(4) NOT NULL default '0',
  `receiving_td` tinyint(4) NOT NULL default '0',
  `receiving_drop` tinyint(4) NOT NULL default '0',
  `receiving_yac` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB ;

[eaglesfan17]

-- --------------------------------------------------------

-- 
-- Table structure for table `game_rushing`
-- 

CREATE TABLE `game_rushing` (
  `id` int(11) NOT NULL auto_increment,
  `game_id` int(11) NOT NULL default '0',
  `team` tinytext NOT NULL,
  `rushing_player` tinytext NOT NULL,
  `rushing_att` tinyint(4) NOT NULL default '0',
  `rushing_yds` int(11) NOT NULL default '0',
  `rushing_avg` float NOT NULL default '0',
  `rushing_long` tinyint(4) NOT NULL default '0',
  `rushing_td` tinyint(4) NOT NULL default '0',
  `rushing_fum` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_settings`
-- 

CREATE TABLE `game_settings` (
  `game_id` int(11) NOT NULL default '0',
  `game_mode` tinytext NOT NULL,
  `quarter_length` varchar(12) NOT NULL default '0',
  `human_qb_accuracy` tinyint(4) NOT NULL default '0',
  `human_pass_blocking` tinyint(4) NOT NULL default '0',
  `human_receiver_catching` tinyint(4) NOT NULL default '0',
  `human_running_ability` tinyint(4) NOT NULL default '0',
  `human_offensive_line_run_blocking` tinyint(4) NOT NULL default '0',
  `human_defensive_awareness` tinyint(4) NOT NULL default '0',
  `human_defensive_line_knockdowns` tinyint(4) NOT NULL default '0',
  `human_interceptions` tinyint(4) NOT NULL default '0',
  `human_defensive_break_blocks` tinyint(4) NOT NULL default '0',
  `human_tackling` tinyint(4) NOT NULL default '0',
  `human_fieldgoal_length` tinyint(4) NOT NULL default '0',
  `human_fieldgoal_accuracy` tinyint(4) NOT NULL default '0',
  `human_punt_length` tinyint(4) NOT NULL default '0',
  `human_punt_accuracy` tinyint(4) NOT NULL default '0',
  `human_kickoff_length` tinyint(4) NOT NULL default '0',
  `cpu_qb_accuracy` tinyint(4) NOT NULL default '0',
  `cpu_pass_blocking` tinyint(4) NOT NULL default '0',
  `cpu_receiver_catching` tinyint(4) NOT NULL default '0',
  `cpu_running_ability` tinyint(4) NOT NULL default '0',
  `cpu_offensive_line_run_blocking` tinyint(4) NOT NULL default '0',
  `cpu_defensive_awareness` tinyint(4) NOT NULL default '0',
  `cpu_defensive_line_knockdowns` tinyint(4) NOT NULL default '0',
  `cpu_interceptions` tinyint(4) NOT NULL default '0',
  `cpu_defensive_break_blocks` tinyint(4) NOT NULL default '0',
  `cpu_tackling` tinyint(4) NOT NULL default '0',
  `cpu_fieldgoal_length` tinyint(4) NOT NULL default '0',
  `cpu_fieldgoal_accuracy` tinyint(4) NOT NULL default '0',
  `cpu_punt_length` tinyint(4) NOT NULL default '0',
  `cpu_punt_accuracy` tinyint(4) NOT NULL default '0',
  `cpu_kickoff_length` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `game_summary`
-- 

CREATE TABLE `game_summary` (
  `game_id` int(11) NOT NULL auto_increment,
  `league_id` tinyint(4) NOT NULL default '0',
  `season` smallint(6) NOT NULL default '0',
  `week` tinyint(4) NOT NULL default '0',
  `home_team` tinytext NOT NULL,
  `away_team` tinytext NOT NULL,
  `game_time` varchar(25) NOT NULL default '0',
  `home_q1` tinyint(4) NOT NULL default '0',
  `home_q2` tinyint(4) NOT NULL default '0',
  `home_q3` tinyint(4) NOT NULL default '0',
  `home_q4` tinyint(4) NOT NULL default '0',
  `home_ot` tinyint(4) NOT NULL default '0',
  `home_final` int(11) NOT NULL default '0',
  `away_q1` tinyint(4) NOT NULL default '0',
  `away_q2` tinyint(4) NOT NULL default '0',
  `away_q3` tinyint(4) NOT NULL default '0',
  `away_q4` tinyint(4) NOT NULL default '0',
  `away_ot` tinyint(4) NOT NULL default '0',
  `away_final` int(11) NOT NULL default '0',
  PRIMARY KEY  (`game_id`)
) ENGINE=InnoDB ;



-- 
-- Table structure for table `game_weather`
-- 

CREATE TABLE `game_weather` (
  `game_id` int(11) NOT NULL default '0',
  `forecast` tinytext NOT NULL,
  `wind` varchar(6) NOT NULL default '0',
  `temp` varchar(12) NOT NULL default '0',
  PRIMARY KEY  (`game_id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `standings`
-- 

CREATE TABLE `standings` (
  `id` int(11) NOT NULL auto_increment,
  `league_id` tinyint(4) NOT NULL default '0',
  `season` smallint(6) NOT NULL default '0',
  `team` varchar(25) NOT NULL default '',
  `wins` tinyint(4) NOT NULL default '0',
  `loses` tinyint(4) NOT NULL default '0',
  `ties` tinyint(4) NOT NULL default '0',
  `win_pct` float NOT NULL default '0',
  `pts_for` int(11) NOT NULL default '0',
  `pts_agst` int(11) NOT NULL default '0',
  `home_wins` tinyint(4) NOT NULL default '0',
  `home_loses` tinyint(4) NOT NULL default '0',
  `home_ties` tinyint(4) NOT NULL default '0',
  `away_wins` tinyint(4) NOT NULL default '0',
  `away_loses` tinyint(4) NOT NULL default '0',
  `away_ties` tinyint(4) NOT NULL default '0',
  `div_wins` tinyint(4) NOT NULL default '0',
  `div_loses` tinyint(4) NOT NULL default '0',
  `div_ties` tinyint(4) NOT NULL default '0',
  `conf_wins` tinyint(4) NOT NULL default '0',
  `conf_loses` tinyint(4) NOT NULL default '0',
  `conf_ties` tinyint(4) NOT NULL default '0',
  `tie_break` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `div_loses` (`div_loses`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

-- 
-- Table structure for table `team`
-- 

CREATE TABLE `team` (
  `id` int(11) NOT NULL auto_increment,
  `league_id` tinyint(4) NOT NULL default '0',
  `city_name` tinytext NOT NULL,
  `nick_name` tinytext NOT NULL,
  `team_abbr` tinytext NOT NULL,
  `conference` tinytext NOT NULL,
  `division` tinytext NOT NULL,
  `bgcolor` varchar(6) NOT NULL default '0',
  `forecolor` varchar(6) NOT NULL default '0',
  `fontcolor1` varchar(6) NOT NULL default '0',
  `fontcolor2` varchar(6) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB ;

-- --------------------------------------------------------

[Roger_Ramjet]

Yep, the whole thing needs to be redesigned. Sorry, but this layout is rubbish.
Now, there are some very good articles here (and elsewhere) about data 'normalisation' and database design, and you need to read them.

Fundamentaly, you should be using many less tables to hold these stats. I don't have time to do anything with this now, but an off-the-top-of-my-head analysis is:

table Teams - hold details about teams
table Players - holds data about individual players, and is linked to Teams since players change teams and their stats can follow them
table Games - details about individual games
table PlayedGame - simple cross ref of the team players in each game: results in a unique id for player-game that you then use in stats table
table StatType - instead of all those fields that you have in all those tables to identify the specific stat, you need a single table that defines each type of stat
table PlayerStats - here you store the stats per game - the core of your DB, and it would go something like this

GameID
PlayerID
StatTypeID
Stat

Thus with your fully normalised data you can calculate
Games played by each Team, and the result
Games played by individual player, the team they were on, and their individual stats for that game

Obviously from these you can then calculate any and all cumulative stats for teams, games and players.

There will of course be several other tables, but these will be defined during the data-normalisation process.