Php Help

[gottimd]

So I am using gstelmacks interrogator to extract game data and upload to an online DB to run scripts off of. I have one that has every year as an option, and the last option is "Career", which should select from table "statsALL". For some reason, when I select it, defaults back to year 1 and only sums that year.

The code I have is:

$year=$_POST['year']; // this picks up any change of year
$selplayers=$_POST['selplayers']; // this picks up the rookies or all players selection

$yearx=$year;

if(!isset($year)){$year='s.year = 2014';$table='stats2014';}
elseif($year=='2014'){$year = 's.year = 2014';$table='stats2014';}
elseif($year=='2013'){$year = 's.year = 2013';$table='stats2013';}
elseif($year=='2012'){$year = 's.year = 2012';$table='stats2012';}
elseif($year=='2011'){$year = 's.year = 2011';$table='stats2011';}
elseif($year=='2010'){$year = 's.year = 2010';$table='stats2010';}
elseif($year=='2009'){$year = 's.year = 2009';$table='stats2009';}
elseif($year=='2008'){$year = 's.year = 2008';$table='stats2008';}
elseif($year=='2007'){$year = 's.year = 2007';$table='stats2007';}
elseif($year=='2006'){$year = 's.year = 2006';$table='stats2006';}
elseif($year=='2005'){$year = 's.year = 2005';$table='stats2005';}
elseif($year=='2004'){$year = 's.year = 2004';$table='stats2004';}
elseif($year=='Career'){$year = 's.year > 2003';$table='statsALL';}


// passing yards
// this is the query for the passing yards
$qrystr = "SELECT ph.playerid, ph.firstname, ph.lastname, t.teamid, s.year, SUM(s.passyards) as stat
FROM (Historical_Player ph LEFT JOIN Active_Player pa ON pa.playerid=ph.playerid)
LEFT JOIN teams t ON pa.team=t.TeamID, $table s
WHERE ph.playerid=s.playerid
AND $year
$isRookie
AND s.week<23
GROUP BY ph.playerid, ph.firstname, ph.lastname, t.teamabbr, s.year
ORDER BY stat DESC
LIMIT $limit;";

Is it because the Group By last paramater is s.year? If it is, how can I write it to say if "Career" is selected, not to group it by that?

[3ric]

I'd try to limit those Group By parameters, only PlayerID and Year would suffice I think.

When Career is selected, you could get another Group By line by including an if statement and appending the new line to the string, like:
if ($career) {
$qrystr = $qrystr + "GROUP BY ph.PlayerID";
} else {
$qrystr = $qrystr + "GROUP BY ph.PlayerID, s.Year";
}
$qrystr = $qrystr + " ORDER BY stat DESC LIMIT $limit;";

[gottimd]

Originally Posted by 3ric

I'd try to limit those Group By parameters, only PlayerID and Year would suffice I think.

When Career is selected, you could get another Group By line by including an if statement and appending the new line to the string, like:
if ($career) {
$qrystr = $qrystr + "GROUP BY ph.PlayerID";
} else {
$qrystr = $qrystr + "GROUP BY ph.PlayerID, s.Year";
}
$qrystr = $qrystr + " ORDER BY stat DESC LIMIT $limit;";

Thanks 3ric!

Unfortunately the report is set up based on those paramaters so I do need all of those Group By's.

so you mean adding what you have into the following:
$qry = select_query2($qrystr);
// i use foreach as an easy way to display each row, you can use while as well
$rank=1;
foreach ($qry as $line){
$pid = $line[0];
$fname = $line[1];
$lname = $line[2];
$tid = $line[3];
$year = $line[4];
$stat = $line[5];

$initial=substr($fname,0,1);


http://www.naflsim.com/webpages/leaders.php
*takes a while to load

[Bonegavel]

I think he meant that you should alter the group by based on $year being 'Career' or not.

something like this? (I like breaking apart my query into chunks so I apologize about my string formatting. Helps me figure out what is what.)

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

if($year=='Career')
{
    $GroupBy = "group by ph.playerid, ph.firstname, ph.lastname, t.teamabbr ";
}
else
{
    $GroupBy = "group by ph.playerid, ph.firstname, ph.lastname, t.teamabbr, s.year ";
}

$qrystr = "SELECT ph.playerid, ph.firstname, ph.lastname, t.teamid, "
         ."s.year, SUM(s.passyards) as stat "
         ."FROM (Historical_Player ph LEFT JOIN Active_Player pa ON pa.playerid=ph.playerid) "
         ."LEFT JOIN teams t ON pa.team=t.TeamID, $table s "
         ."WHERE (ph.playerid=s.playerid AND $year $isRookie) "
         ."AND s.week<23 "
         ."$GroupBy "
         ."ORDER BY stat DESC "
         ."LIMIT $limit";

[marcmoustache]

Yep, sorry gottimd, my dodgy code, what bonegavel said will work

[gottimd]

Thanks guys...so I can just add it to this section?

// end of funtions

$year=$_POST['year']; // this picks up any change of year
$selplayers=$_POST['selplayers']; // this picks up the rookies or all players selection

$yearx=$year;

if(!isset($year)){$year='s.year = 2014';$table='stats2014';}
elseif($year=='2014'){$year = 's.year = 2014';$table='stats2014';}
elseif($year=='2013'){$year = 's.year = 2013';$table='stats2013';}
elseif($year=='2012'){$year = 's.year = 2012';$table='stats2012';}
elseif($year=='2011'){$year = 's.year = 2011';$table='stats2011';}
elseif($year=='2010'){$year = 's.year = 2010';$table='stats2010';}
elseif($year=='2009'){$year = 's.year = 2009';$table='stats2009';}
elseif($year=='2008'){$year = 's.year = 2008';$table='stats2008';}
elseif($year=='2007'){$year = 's.year = 2007';$table='stats2007';}
elseif($year=='2006'){$year = 's.year = 2006';$table='stats2006';}
elseif($year=='2005'){$year = 's.year = 2005';$table='stats2005';}
elseif($year=='2004'){$year = 's.year = 2004';$table='stats2004';}
elseif($year=='Career'){$year = 's.year > 2003';$table='statsALL';}

if($year=='Career'){$GroupBy = "group by ph.playerid, ph.firstname, ph.lastname, t.teamabbr ";}
else{$GroupBy = "group by ph.playerid, ph.firstname, ph.lastname, t.teamabbr, s.year ";}

And then change the group by in each statement to say $GroupBy, correct?

[marcmoustache]

yep

[gstelmack]

PHP doesn't let you write that block like:

if(!isset($year)){$year='s.year = 2014';$table='stats2014';}
elseif($year=='Career'){$year = 's.year > 2003';$table='statsALL';}
else{$year = 's.year = '+$year;$table='stats'+$year;}

?

[Icy]

Originally Posted by gstelmack

PHP doesn't let you write that block like:

if(!isset($year)){$year='s.year = 2014';$table='stats2014';}
elseif($year=='Career'){$year = 's.year > 2003';$table='statsALL';}
else{$year = 's.year = '+$year;$table='stats'+$year;}

?

Yup, good way to group all the conditions, but in this case there is a problem with this:

$year = 's.year = '+$year; and then this $table='stats'+$year;

As in that sentence, and for example for 2014

$table = 'statss.year = 2014'

instead of what is wanted:$table = stats2014

the problem is using the same variable $year for two different things, instead i would use two variables, like $year and $yearQ.
That way i would use $year always to store the numerical year, and $yearQ to store the variable for the query,

for example if the year is 2014:

$year = 2014
$yearQ = 's.year = '+$year; (so $yearQ = 's.year = 2014')
$table = 'stats' + $year; (so $table = 'stats2014')

[gstelmack]

Yeah, I wasn't paying close attention to syntax details (I know very little PHP), was just surprised at the giant IF block.

[gottimd]

Originally Posted by gstelmack

Yeah, I wasn't paying close attention to syntax details (I know very little PHP), was just surprised at the giant IF block.

I have been learning in my spare 3 minutes that I have a day not working. Marcmoustache and Icy have been very helpful in guiding me in the right direction as I have been constantly screwing it up trying to learn php on my own. I think I am getting the hang of it, and I hope to be able to generate better reports for my online league with the info you keep putting out on the FOF Extractor!

[gstelmack]

Originally Posted by gottimd

I have been learning in my spare 3 minutes that I have a day not working. Marcmoustache and Icy have been very helpful in guiding me in the right direction as I have been constantly screwing it up trying to learn php on my own. I think I am getting the hang of it, and I hope to be able to generate better reports for my online league with the info you keep putting out on the FOF Extractor!

Sorry, I'm doing lots of phone screens and interviews, so my code critique meter is pegged at the moment...