Using 2 queries with one variable

[eaglesfan17]

I made an attempt at a portion of my code using 2 queries for a variable, can someone point out my mistake?



$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>";

}

[Kid Charming]

Hi. When posting, please describe the specific problem you're having, including any error messages you may be getting (or if you're not getting any). The more we know about what's happening that shouldn't be (or what isn't that should be), the better we'll know what to look for in your code.

[eaglesfan17]

Well, the problem is that I'm not getting any data returned and I'm not even sure if the way I have the array is set up properly at all...I'm trying to get "opponent rushing yards" for $team1 and $team2 if they = home_team and when they = away_team, then add those totals together and echo them to the browser.

[Kid Charming]

Previous response deleted, 'coz I was being dumb.

Chances are, too, that you can get your results with one query. Can you post your table structure and give an example of what you're trying to get out of it?

[eaglesfan17]

First off, table structure:

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. 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)
{

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>";

[Kid Charming]

Ok, so I had a lapse -- that while(list()) construct is ok, so nevermind that part. :p

You're setting $totalyards as a regular variable instead of an array inside your while() -- this means you're overwriting it with each iteration. I think you need something like:


while...
{
$totalyards[] = $opp_rushyds + $opp_rushyds2;
}


Your foreach, though is looking for $opp_rushing_results, but I don't see it being set up anywhere. Where is it coming from?

Also, if you'd like, we can try to get this down to one query, but I'll need the structure of your second table, too.

[eaglesfan17]

There is only one table, but I thought I'd have to use 2 queries for what I was trying to do...

[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 words "Array" for each team.

[eaglesfan17]

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

[Kid Charming]

Your array has another dimension to it -- try:


$teamdata_opp_rushing[$team1][$team2]


and see if that gives you a number instead of 'Array'. It can also help to run your $teamdata_opp_rushing through print_r() to see how it's set up.

[eaglesfan17]

Which instance of $teamdata_opponent_rushing should I change? All?

[eaglesfan17]

Hmm tried it in this line...



foreach ($opp_rushing_results as $opp_rushing_stat => $teamdata_opp_rushing[$team1][$team2]) {



Now I'm getting the word "Array" once instead of twice...See here:

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

[Kid Charming]

Change them in your actual echo statement. You can only echo specific elements of an array; if you try to echo the array itself, you just get the 'Array' that you've already seen.

[eaglesfan17]

Am I even using the right format for what I'm trying to do? I'm extremely confused :confused:

[Kid Charming]

Assuming the information's correct, you can set your results up like this. Did you try running the array through print_r()?

[eaglesfan17]

Yes, I did run the array through print_r and I get this:



Array ( [Opponent Rushing Yds] => Array ( [Eagles] => Array ( [Eagles] => 348 ) ) ) Array ( [Opponent Rushing Yds] => Array ( [Eagles] => Array ( [Eagles] => 348 ) [Chiefs] => Array ( [Chiefs] => 54 ) ) )



Which is SO wrong, the 'Eagles' should have "9" opp_rushing_yds, and the 'Chiefs' should have "1".

[Kid Charming]

Then your query's pulling wrong info. You've got two tables joined together -- game_rushing and game_summary. I assume the table you've posted is game_rushing. What's the structure for game_summary?

[eaglesfan17]

game_summary
Field Type Null Default
game_id int(11) No
league_id tinyint(4) No 0
season smallint(6) No 0
week tinyint(4) No 0
home_team tinytext No
away_team tinytext No
game_time varchar(25) No 0
home_q1 tinyint(4) No 0
home_q2 tinyint(4) No 0
home_q3 tinyint(4) No 0
home_q4 tinyint(4) No 0
home_ot tinyint(4) No 0
home_final int(11) No 0
away_q1 tinyint(4) No 0
away_q2 tinyint(4) No 0
away_q3 tinyint(4) No 0
away_q4 tinyint(4) No 0
away_ot tinyint(4) No 0
away_final int(11) No 0

[eaglesfan17]

Figured that out!! My query should be::



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
away_team=team AND ((home_team = '$team1') OR (home_team = '$team2')) group by away_team

[eaglesfan17]

Now, I need help to figure out what is wrong with my array.

[Kid Charming]

And you want the total rushing yards the Chiefs got vs. the Eagles in one season, and the total rushing yards the Eagles got vs. the Chiefs in the same season?

[eaglesfan17]

Well crap, now it's doubling the "opp_rushing_yds"

[eaglesfan17]

And you want the total rushing yards the Chiefs got vs. the Eagles in one season, and the total rushing yards the Eagles got vs. the Chiefs in the same season?


No, what I want is the Opponent's rushing yards from the Eagles and from the Chiefs, them being relative is nothing to do with it, I'm making a team comparison, check here:

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


That's what I'm trying to accomplish.

[Kid Charming]

I just checked the link, and it looks like you may be onto it, looking at the array you're printing. You still snagging?

[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...

[Kid Charming]

I think you can just use this:


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

[eaglesfan17]

Man, It's so close, if I use [$home], it only gets the yds if they are the home team, if I use [$away2] it only gets them if $team1 or $team2 is the away team...is there a way to do both?

[Kid Charming]

Try taking the '/2' off your formula when you set $totalyards.

[eaglesfan17]

Still not working dude, do you have any idea how I could change my echo statement or anything?

[eaglesfan17]

Here is what the code looks like as of now:



$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($query2);



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] = $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>";
}

[Kid Charming]

Echo '$teamdata_opp_rushing[$team2][0]' and see what that gives you.