Getting the opposite of the output I want...

eaglesfan17

Alright, let's see if we can tackle this one...

I have a table in my database that has one field named "home_team" and one named "away_team".

I need to draw the away_team passing_yds if home_team = $team1 or $team2...

Here is what I have....

    $query = "select away_team, sum(rushing_yds) as opp_rushyds
     from game_summary, game_rushing
     where game_summary.game_id=game_rushing.game_id
     AND ((home_team = '$team1')OR (home_team = '$team2'))
     and league_id=1 and
     season=$season and
     week<18
     group by away_team";

$res = mysql_query($query,$db);

while (list( $away_team, $opp_rushyds )  = mysql_fetch_row($res)) {

   ## store data in an array
   $opp_rushing_results['Opponent Rushing Yds'][$team] = $opp_rushyds;
   }

   foreach ($opp_rushing_results as $opp_rushing_stat => $teamdata_opp_rushing)
{
      echo "<tr><td width=\"205\" bgcolor=\"#CCCCCC\"> $opp_rushing_stat  </td><td width=\"50\" bgcolor=\"#CCCCCC\"> $teamdata_opp_rushing[$team1] </td><td align=\"right\" bgcolor=\"#CCCCCC\"> $teamdata_opp_rushing[$team2] </td></tr>";
}

This gets the information I want from the database, BUT since the fields it returns from the database are the "away_team", I get nothing displayed for my home_team (being the team I want to "opp_rushyds" for).

I know that's hard to follow, but bare with me.

leatherback

Unless I am missing a crtical detail:

 $query = "select away_team, home_team, sum(rushing_yds) as opp_rushyds
     from game_summary, game_rushing
     where game_summary.game_id=game_rushing.game_id
     AND ((home_team = '$team1')OR (home_team = '$team2'))
     and league_id=1 and
     season=$season and
     week<18
     group by away_team";

WHY do you store the info in an array?

eaglesfan17

I'm storing it in an array because that's how I've been doing the rest of the script...I need to draw the "opp_rushyds" for both teams and display them...I don't really know how to do it another way...

eaglesfan17

Hey wait, I figured it out...Now I need to ask a tough one...How do I use 2 queries for one variable?

Like I want to use this query:

    $query = "select home_team, away_team, sum(rushing_yds) as opp_rushyds
	 from game_summary, game_rushing 
	 where game_summary.game_id=game_rushing.game_id
	 AND ((home_team = '$team1')OR (home_team = '$team2')) 
	 and league_id=1 and 
	 season=$season and 
	 week<18 
	 group by away_team";

and this query:

    $query = "select away_team, home_team, sum(rushing_yds) as opp_rushyds2
	 from game_summary, game_rushing 
	 where game_summary.game_id=game_rushing.game_id
	 AND ((away_team = '$team1')OR (away_team = '$team2')) 
	 and league_id=1 and 
	 season=$season and 
	 week<18 
	 group by home_team";

Then combine "opp_rushyds" and "opp_rushyds2" as "Opponent_rushing_yds". Is this possible?

eaglesfan17

I made an attempt at a query, can someone tell me where I went wrong?

$query = "select home_team as home, away_team as away, sum(rushing_yds) as opp_rushyds from game_summary, game_rushing where game_summary.game_id=game_rushing.game_id AND ((home_team = '$team1') OR (home_team = '$team2')) and league_id=1 and season=$season and week<18 group by away_team";
$result = mysql_query($query);

$query2 = "select away_team as away2, home_team as home2, sum(rushing_yds) as opp_rushyds2 from game_summary, game_rushing where game_summary.game_id=game_rushing.game_id AND ((away_team = '$team1')OR (away_team = '$team2')) and league_id=1 and season=$season and week<18 group by home_team";
$result2 = mysql_query($query);

while(list($home, $away, $opp_rushyds) = mysql_fetch_row($result)){
   list($away2, $home2, $opp_rushyds2) = mysql_fetch_row($result2);
   $totalyards = $opp_rushyds + $opp_rushyds2;
}   



foreach ($opp_rushing_results as $opp_rushing_stat => $teamdata_opp_rushing) {
   $opp_rushing_results['Opponent Rushing Yds'][$home][$away2] = $totalyards;


  echo "<tr><td width=\"205\"> $opp_rushing_stat </td><td width=\"50\"> $teamdata_opp_rushing[$team1] </td><td align=\"right\"> $teamdata_opp_rushing[$team2] </td></tr>";

}

eaglesfan17

Here, let me rephrase...

First off, here is the table structure for the table i'll be using and referring to:


Field  	            Type   	Null  	Default
id  	             int(11) 	   No  	 
game_id  	 int(11) 	No  	0 
team  	          tinytext 	 No  	 
rushing_player  tinytext       No  	 
rushing_att  	tinyint(4) 	No  	0 
rushing_yds  	int(11) 	No  	0 
rushing_avg  	float 	         No  	0 
rushing_long  	tinyint(4) 	No  	0 
rushing_td  	tinyint(4) 	No  	0 
rushing_fum  	tinyint(4) 	No  	0

What I'm trying to do is get sum(rushing_yds) for ['$team1','$team2'] if ['$team1','$team2'] = home_team; then, if ['$team1','$team2'] = away_team...combine the two as "$opponent_rushing_yds" and echo them to the browser.

But this isn't working, here is my code for trying to do it which displays nothing.


 $query = "select home_team as home, away_team as away, sum(rushing_yds) as opp_rushyds from game_summary, game_rushing where game_summary.game_id=game_rushing.game_id AND ((home_team = '$team1') OR (home_team = '$team2')) and league_id=1 and season=$season and week<18 group by away_team";
$result = mysql_query($query);

$query2 = "select away_team as away2, home_team as home2, sum(rushing_yds) as opp_rushyds2 from game_summary, game_rushing where game_summary.game_id=game_rushing.game_id AND ((away_team = '$team1')OR (away_team = '$team2')) and league_id=1 and season=$season and week<18 group by home_team";
$result2 = mysql_query($query);

while(list($home, $away, $opp_rushyds) = mysql_fetch_row($result)){
   list($away2, $home2, $opp_rushyds2) = mysql_fetch_row($result2);
   $totalyards = $opp_rushyds + $opp_rushyds2;
}   



foreach ($opp_rushing_results as $opp_rushing_stat => $teamdata_opp_rushing) {
   $opp_rushing_results['Opponent Rushing Yds'][$home][$away2] = $totalyards;


  echo "<tr><td width=\"205\"> $opp_rushing_stat </td><td width=\"50\"> $teamdata_opp_rushing[$team1] </td><td align=\"right\"> $teamdata_opp_rushing[$team2] </td></tr>";

}

So far I have done this with other statistics from different tables as I will show in one second, the tables have different structures, but I was attempting to use the same format.

Other stats:


$query = "SELECT team, sum(rushing_att) as rushing_att, sum(rushing_yds) as rushing_yds, (sum( rushing_yds ) / sum( rushing_att )) AS rushing_avg, sum(rushing_td) as rushing_td, sum(rushing_fum) as rushing_fum, (sum( rushing_yds ) / count( DISTINCT game_rushing.game_id )) AS rushing_yds_per_game
		FROM game_rushing, game_summary
		WHERE game_summary.game_id = game_rushing.game_id
		AND league_id =1
		AND season = 2006
		AND team IN ('$team1', '$team2')
		GROUP BY team";

$res=mysql_query($query,$db);

while (list( $team, $rushing_att, $rushing_yds, $rushing_avg, $rushing_td, $rushing_fum, $rushing_yds_per_game )  = mysql_fetch_row($res)) {

   ## store data in an array
   $rushing_results['Rushing Attempts'][$team] = $rushing_att;
   $rushing_results['Rushing Yards'][$team] = $rushing_yds;
   $rushing_results['Rushing Average'][$team] = $rushing_avg;
   $rushing_results['Rushing TD'][$team] = $rushing_td;
   $rushing_results['Rushing Fumbles'][$team] = $rushing_fum;
   $rushing_results['Rushing Yds Per Game'][$team] = $rushing_yds_per_game;
}


## At end, print the array in columns


foreach ($rushing_results as $rushing_stat => $teamdata_rushing) 
{

   if ($alternate == 0)
   {
      echo "<tr><td width=\"205\" bgcolor=\"#CCCCCC\"> $rushing_stat  </td><td width=\"50\" bgcolor=\"#CCCCCC\"> $teamdata_rushing[$team1] </td><td align=\"right\" bgcolor=\"#CCCCCC\"> $teamdata_rushing[$team2] </td></tr>";
      $alternate++;
   }
   else
   {
      echo "<tr><td width=\"205\"> $rushing_stat </td><td width=\"50\"> $teamdata_rushing[$team1] </td><td align=\"right\"> $teamdata_rushing[$team2] </td></tr>";
      $alternate = 0;

   }


}

Can someone help me here? Where am I going wrong?

eaglesfan17

I posted my code a little wrong....Here is what it should have read...

while(list($home, $away, $opp_rushyds) = mysql_fetch_row($result)){
   list($away2, $home2, $opp_rushyds2) = mysql_fetch_row($result2);
      $totalyards = $opp_rushyds + $opp_rushyds2;

   $opp_rushing_results['Opponent Rushing Yds'][$home][$away2] = $totalyards;
}   



foreach ($opp_rushing_results as $opp_rushing_stat => $teamdata_opp_rushing) {


  echo "<tr><td width=\"205\"> $opp_rushing_stat </td><td width=\"50\"> $teamdata_opp_rushing[$team1] </td><td align=\"right\"> $teamdata_opp_rushing[$team2] </td></tr>";

}

This displays "Oponent Rushing Yards"...But then for each team it displays the word "Array" for each team.

Example: http://pmlfootball.net/matchup2.php?team1=Eagles&team2=Chiefs

eaglesfan17

Okay I'm close, but I'm still getting 'Array' when I try to view the stats, here is the current code...


$query = "select home_team as home, away_team as away, sum(rushing_yds) as opp_rushyds from game_summary, game_rushing where game_summary.game_id=game_rushing.game_id AND ((home_team = '$team1') OR (home_team = '$team2')) and away_team=team and league_id=1 and season=$season and week<18 group by away_team";
$result = mysql_query($query);

$query2 = "select away_team as away2, home_team as home2, sum(rushing_yds) as opp_rushyds2 from game_summary, game_rushing where game_summary.game_id=game_rushing.game_id AND ((away_team = '$team1')OR (away_team = '$team2')) and home_team=team and league_id=1 and season=$season and week<18 group by home_team";
$result2 = mysql_query($query);



while(list($home, $away, $opp_rushyds) = mysql_fetch_row($result)){
   list($away2, $home2, $opp_rushyds2) = mysql_fetch_row($result2);

  $totalyards = ("$opp_rushyds" + "$opp_rushyds2")/2;


   $opp_rushing_results['Opponent Rushing Yds'][$home][$away2] = $totalyards;
   print_r ($opp_rushing_results);

}   

   foreach ($opp_rushing_results as $opp_rushing_stat => $teamdata_opp_rushing) {
      echo "<tr><td width=\"205\" bgcolor=\"#CCCCCC\"> Opponent Rushing Yards  </td><td width=\"50\" bgcolor=\"#CCCCCC\"> $teamdata_opp_rushing[$team1] </td><td align=\"right\" bgcolor=\"#CCCCCC\"> $teamdata_opp_rushing[$team2] </td></tr>";
}

I think my problem lies here:


   $opp_rushing_results['Opponent Rushing Yds'][$home][$away2] = $totalyards;

But I don't know how to fix it...

eaglesfan17

I was right...This line is the problem (I think)...


   $opp_rushing_results['Opponent Rushing Yds'][$home][$away2] = $totalyards;

Okay, If I change that to read just [$home], It only retrives "opponent rushing yards" for $team1 and $team2 if they are home team and if I use [$away2], It only retrieves if they are the away team, but I need it to combine...Where does the problem lie?