Group by, passing var to url

[esiason14]

I'm using the following to view the weekly stat leaders. This works great when I only have one year (2005) of stats in the db. What I'm trying to do is something like this, but I'm having trouble getting started:

If theres more than one year of stats (ex 2003, 2004 and 2005), I need to isolate the weekly leaders for each specific year.

I'm guessing that I'm going to have to group by week where year = $year...

So I'm also guessing that I'm going to have to add a variable, year to the following links as well.

   $links_html .= "<a href='/football/passingleaders.php?week=" . $week["week"] . "'>" . $week["week"] . ""."</a> | ";
     }

Anyway, any help in getting me in the right direction would be appreciated :

<?php
$public=1;
require_once("config.php");
require_once($DOC_ROOT . "/lib/header.php");

 // select weeks first, and make links

  $query = "select week from playerstats group by week";
  $result = mysql_query($query);
  while($week = mysql_fetch_array($result)){
    // build html for links

   $links_html .= "<a href='/football/passingleaders.php?week=" . $week["week"] . "'>" . $week["week"] . ""."</a> | ";
 }

  // print links here

print "<table align=\"center\">";
print "<tr>";
print "<td>Select a Week: </td>";
  echo "<td align=\"center\">$links_html</td>";
echo "</tr> </table>";

  // now select for this specific week

 $week = $_GET["week"];
if(empty($week))
  $week = 1;


print "<h2 align=\"center\">Week " . $week . ": Quarterback Leaders</h2>";
?>

<?php

 $query = "SELECT lname, fname, rush_att, rush_yd, rush_td, pass_att, pass_cmp, pass_td, pass_yd, playerstats.int, fmbl_lost,

twopt, playerstats.player_id, players.nflteam_id, nflteam_abbv, players.position_id
  FROM playerstats, players, nflteams, positions
  WHERE playerstats.player_id = players.player_id
  AND players.position_id = positions.position_id
  AND players.position_id = 1
  AND nflteams.nflteam_id = players.nflteam_id
  AND nflteam_abbv = nflteams.nflteam_abbv
  AND week = '" . $week . "'
  ORDER by pass_yd DESC
  LIMIT 29";

   $result = mysql_query($query);

   //checking output

   $num_results = mysql_num_rows($result) or die(mysql_error());
   if ($num_results == 0)
   {
      echo "No Results Found";
   }
   else
   {

 ?>
<p align="left" class="small">* Click on headers to sort stats.</p>
<table align="center" width="75%" border="1" cellpadding="2" cellspacing="0">
	<TR>
   		<TD width="38%" VALIGN="middle" BGCOLOR="#AB0507">&nbsp;</TD>
   		<TD width="34%" align="center" VALIGN="middle" BGCOLOR="#AB0507"><font

color="#FFFFFF"><b>PASSING</b></font></TD>
		<TD width="12%" align="center" BGCOLOR="#AB0507"><font color="#FFFFFF"><B>TO</B></font></TD>
		<TD width="16%" align="center" BGCOLOR="#AB0507"><font color="#FFFFFF"><B>Rushing</B></font></TD>
	</TR></table>
<table bgcolor="#CCCCCC" class="sortable" id="passing" align="center" width="75%" border="1" cellpadding="2" cellspacing="0">
    <tr>
         <th><b><u>Player</u></b></th>
         <th><b><u>Team</b></u></th>
         <th><b><u>FFR Pts</b></u></th>
         <th><b><u>Cmp</b></u></th>
         <th><b><u>Att</b></u></th>
         <th><b><u>Cmp%</b></u></th>
         <th><b><u>YD</b></u></th>
         <th><b><u>TD</b></u></th>
         <th><b><u>Int</b></u></th>
         <th><b><u>Fum</b></u></th>
         <th><b><u>Att</b></u></th>
         <th><b><u>YD</b></u></th>
         <th><b><u>TD</b></u></th>
    </tr>

<?php
 $i = 0;
      while($row = MySQL_fetch_array($result)) {

    $lname = $row['lname'];
    $fname = $row['fname'];
    $rush_yd = $row['rush_yd'];
    $rush_att = $row['rush_att'];
    $rush_td = $row['rush_td'];
    $pass_att = $row['pass_att'];
    $pass_yd = $row['pass_yd'];
    $pass_td = $row['pass_td'];
    $pass_cmp = $row['pass_cmp'];
    $cmppct = $pass_cmp / $pass_att * 100;
    $cmppctround = ROUND($cmppct, 1);
    $int = $row['int'];
$twopt = $row['twopoint'];
    $week = $row['week'];
$player_id = $row['player_id'];
$nflteamabbv = $row['nflteam_abbv'];
$fumble = $row['fmbl_lost'];
$points = (($pass_yd * 0.034) + ($pass_td * 6) + (twopt * 2) + ($int * -2) +  ($fumble * -2) + ($rush_yd * 0.067) +
($rush_td * 6));
	$pointsround = ROUND($points, 1);


echo " <tr><td><a href='/football/players.php?player_id=" . $player_id . "'> ".$fname."
".$lname."</td>";
echo "<td>".$nflteamabbv."</td>";
echo "<td align=\"center\">".$pointsround."</td>";
echo "<td align=\"center\">".$pass_cmp."</td>";
echo "<td align=\"center\">".$pass_att."</td>";
echo "<td align=\"center\">".$cmppctround."</td>";
echo "<td align=\"center\">".$pass_yd."</td>";
echo "<td align=\"center\">".$pass_td."</td>";
echo "<td align=\"center\">".$int."</td>";
echo "<td align=\"center\">".$fumble."</td>";
echo "<td align=\"center\">".$rush_att."</td>";
echo "<td align=\"center\">".$rush_yd."</td>";
echo "<td align=\"center\">".$rush_td."</td>";

        }
   }

  ?>

</table>

<div align="center"><?php require($DOC_ROOT . "/lib/footer.php"); ?></div>

[tony_l]

Have you tried the GROUP BY syntax in your SQL query? You can do that and it should work fine. No need to add another variable. I do not know the design of your table, but looking at the select statement you already a week variable, so depending on if that includes the year, you are set. Here is the link to the GROUP BY syntax -

http://dev.mysql.com/doc/refman/4.1/en/select.html

[esiason14]

Hi Tony,
Yes, I tried using GROUP by year, but didnt get the results I had hoped for:

When I try to use group by, I get a week listing like this..for all the years:

123456789101112131415161712345678910111213141516171234567

The groups are grouped by year, but are all echoed on the same page. What I'm trying to do is:

If a user selects a year, only the weeks associated with that year will be shown.

Though I dont know a lot, I'm thinking that I might have to pass an additional variable to the url...like this?

www.site.com/passingleaders?year=2003?week=8

If not, I guess I need some help on the query-side? Any more advice?

[esiason14]

Ok, perhaps..and hopefully I'm getting closer.

http://www.fantasyfootballresearch.com/football/passingleaders.php

Any more advice would again be appreciated

<script src="sortable.js"></script>
<?php
$public=1;
require_once("config.php");
require_once($DOC_ROOT . "/lib/header.php");

  $query = "select year from playerstats
  group by year";
  $result = mysql_query($query);
  while($year = mysql_fetch_array($result)){
    // build html for links

$yrlinks_html .= "<a href='/football/passingleaders.php?year=" . $year["year"] . "'>" . $year["year"] . ""."</a> | ";
     }

$year = $row['year'];
 // select weeks first, and make links

  $query = "select week, year from playerstats group by week";
  $result = mysql_query($query);
 while (list ($week, $year) = mysql_fetch_array($result)){
    // build html for links

$links_html .= "<a href='/football/passingleaders.php?year=" . $year . ""."&week=" .
$week["week"] . "'>" . $week["week"] . ""."</a> | ";
     }

  // now select for this specific week/year....hopefully
  // print links here

echo "<table align=\"center\">";
echo "<tr>";
echo "<td>Select a Year: </td>";
echo "<td align=\"center\">$yrlinks_html</td>";
echo "<td>Select a Week: </td>";
echo "<td align=\"center\">$links_html</td>";
echo "</tr> </table>";

  // now select for this selected year/specific week

 $week = $_GET["week"];
if(empty($week))
  $week = 1;

 $year = $_GET["year"];
if(empty($year))
  $year = 2005;


print "<h2 align=\"center\">Year: " . $year . "</h2>";
print "<h2 align=\"center\">Week " . $week . ": Quarterback Leaders</h2>";

?>
<p class="divtest" align="center"> Position: <a

href="http://www.fantasyfootballresearch.com/football/passingleaders.php">QB</a> | <a

href="http://www.fantasyfootballresearch.com/football/rushingleaders.php">RB</a> | <a

href="http://www.fantasyfootballresearch.com/football/receivingleaders.php">WR</a> | <a

href="http://www.fantasyfootballresearch.com/football/tereceivingleaders.php">TE</a> | <a

href="http://www.fantasyfootballresearch.com/football/kickingleaders.php">K</a> | <a

href="http://www.fantasyfootballresearch.com/football/defensiveleaders.php">DEF</a></p>

<p align="center"><a href="http://www.fantasyfootballresearch.com/football/seasonQBleaders.php">View Season Leaders</a></p>

<?php

$query = "SELECT lname, fname, rush_att, rush_yd, rush_td, pass_att, pass_cmp, pass_td, pass_yd, playerstats.int, fmbl_lost,
twopt, playerstats.player_id, players.nflteam_id, nflteam_abbv, players.position_id
  FROM playerstats, players, nflteams, positions
  WHERE playerstats.player_id = players.player_id
  AND players.position_id = positions.position_id
  AND players.position_id = 1
  AND nflteams.nflteam_id = players.nflteam_id
  AND nflteam_abbv = nflteams.nflteam_abbv
  AND week = '" . $week . "'
  ORDER by pass_yd DESC
  LIMIT 29";

   $result = mysql_query($query);

   //checking output

   $num_results = mysql_num_rows($result) or die(mysql_error());
   if ($num_results == 0)
   {
      echo "No Results Found";
   }
   else
   {

 ?>
<p align="left" class="small">* Click on headers to sort stats.</p>
<table align="center" width="75%" border="1" cellpadding="2" cellspacing="0">
	<TR>
   		<TD width="38%" VALIGN="middle" BGCOLOR="#AB0507">&nbsp;</TD>
   		<TD width="34%" align="center" VALIGN="middle" BGCOLOR="#AB0507"><font color="#FFFFFF"><b>PASSING</b></font></TD>
		<TD width="12%" align="center" BGCOLOR="#AB0507"><font color="#FFFFFF"><B>TO</B></font></TD>
		<TD width="16%" align="center" BGCOLOR="#AB0507"><font color="#FFFFFF"><B>Rushing</B></font></TD>
	</TR></table>
<table bgcolor="#CCCCCC" class="sortable" id="passing" align="center" width="75%" border="1" cellpadding="2" cellspacing="0">
    <tr>
         <th><b><u>Player</u></b></th>
         <th><b><u>Team</b></u></th>
         <th><b><u>FFR Pts</b></u></th>
         <th><b><u>Cmp</b></u></th>
         <th><b><u>Att</b></u></th>
         <th><b><u>Cmp%</b></u></th>
         <th><b><u>YD</b></u></th>
         <th><b><u>TD</b></u></th>
         <th><b><u>Int</b></u></th>
         <th><b><u>Fum</b></u></th>
         <th><b><u>Att</b></u></th>
         <th><b><u>YD</b></u></th>
         <th><b><u>TD</b></u></th>
    </tr>

<?php
 $i = 0;
      while($row = MySQL_fetch_array($result)) {

    $lname = $row['lname'];
    $fname = $row['fname'];
    $rush_yd = $row['rush_yd'];
    $rush_att = $row['rush_att'];
    $rush_td = $row['rush_td'];
    $pass_att = $row['pass_att'];
    $pass_yd = $row['pass_yd'];
    $pass_td = $row['pass_td'];
    $pass_cmp = $row['pass_cmp'];
    $cmppct = $pass_cmp / $pass_att * 100;
    $cmppctround = ROUND($cmppct, 1);
    $int = $row['int'];
$twopt = $row['twopoint'];
    $week = $row['week'];
$player_id = $row['player_id'];
$nflteamabbv = $row['nflteam_abbv'];
$fumble = $row['fmbl_lost'];
$points = (($pass_yd * 0.034) + ($pass_td * 6) + (twopt * 2) + ($int * -2) + 
    ($fumble * -2) + ($rush_yd * 0.067) + ($rush_td * 6));
$pointsround = ROUND($points, 1);

echo " <tr><td><a href='/football/players.php?player_id=" . $player_id . "'> ".$fname."
".$lname."</td>";
echo "<td>".$nflteamabbv."</td>";
echo "<td align=\"center\">".$pointsround."</td>";
echo "<td align=\"center\">".$pass_cmp."</td>";
echo "<td align=\"center\">".$pass_att."</td>";
echo "<td align=\"center\">".$cmppctround."</td>";
echo "<td align=\"center\">".$pass_yd."</td>";
echo "<td align=\"center\">".$pass_td."</td>";
echo "<td align=\"center\">".$int."</td>";
echo "<td align=\"center\">".$fumble."</td>";
echo "<td align=\"center\">".$rush_att."</td>";
echo "<td align=\"center\">".$rush_yd."</td>";
echo "<td align=\"center\">".$rush_td."</td>";

        }
   }

  ?>


</table>


<div align="center"><?php require($DOC_ROOT . "/lib/footer.php"); ?></div>