Page 1 of 1

php help

Posted: Fri Oct 28, 2005 8:26 pm
by esiason14
I working on a football site. I'm trying to display yearly stat leaders, broken down by week. This works perfectly if I only have one year worth of stats in the database..If I have more than one, thats were I run into trouble. Basically this is what I want to do.

Display the years in db, like this:

Code: Select all

2003 | 2004 | 2005
then when a user selects a year, the weeks associated with that year will be shown like this:

Code: Select all

1  | 2 | 3 | 4 | etc....
I think I'm getting close, but I have this problem..all the years are shown, but so are all of the weeks at one time...

see here:

http://www.fantasyfootballresearch.com/ ... eaders.php

Here is my code so far...please help!! :)

Code: Select all

$query = "select week, 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> | ";
     }


 // select weeks first, and make links

  $query = "select week, year from playerstats group by week, year";
  $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 = 12;

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

Posted: Fri Oct 28, 2005 8:41 pm
by feyd
look into GROUP BY syntax for your SQL..

And try to come up with a bit more descriptive topic titles.. ;)

Posted: Fri Oct 28, 2005 8:49 pm
by esiason14
feyd wrote:look into GROUP BY syntax for your SQL..

And try to come up with a bit more descriptive topic titles.. ;)
I've tried group by, but keep getting undesired results...

and sorry about the title...it is pretty bad :(

Posted: Fri Oct 28, 2005 8:56 pm
by RobertGonzalez
Two queries, with an isset if statement in the second.

Query one grabs all years grouped by year. Within the While loop for displaying years, check to see if the year was chosen ( if (isset($_GET['year'])) ) and if so, run another query where year = $_GET['year'].

Does this make any sense? I don't want to write the code for you (what fun would that be for you?) but it is fairly common setup in PHP link lists (used even in this very community script to show all forums within a category, all posts within a forum, all threads within a post... you get the idea).

Posted: Fri Oct 28, 2005 9:51 pm
by esiason14
Everah wrote:Does this make any sense?
I think so..let me give it a shot and I'll post back here
Everah wrote: I don't want to write the code for you (what fun would that be for you?)
Actually I wouldnt mind at all...This has been killing me for the past 48 hours :wink:

Posted: Fri Oct 28, 2005 10:33 pm
by esiason14
well, if my goal was to produce several parse errors..then I would have been successfull...

Anyways, this is the last thing I tried:

Code: Select all

$query = "select year from playerstats
  group by year";
  $result = mysql_query($query);
  while($year = mysql_fetch_array($result)){

 if (isset($_GET['year']))
{
$year = $_GET["year"];
  $query = "select week, year from playerstats where year = ".$_GET['year']." group by week";
  $result = mysql_query($query);
 while ($week = mysql_fetch_array($result))
Please help me correct this:

Posted: Sat Oct 29, 2005 12:08 am
by RobertGonzalez
I would do something like this...

Code: Select all

$query = "SELECT DISTINCT year FROM playerstats GROUP BY year";
if (!$result = mysql_query($query))
{
    die("Could not execute query <strong>$query</strong> because " . mysql_error() . ".<br />") ;
}

$year_list = array();
while ($row = mysql_fetch_array($result))
{
    $year_list[] = $row;
}

$query = "SELECT week, year FROM playerstats ORDER BY week";
if (!result = mysql_query($query))
{
    die("Could not execute query <strong>$query</strong> because " . mysql_error() . ".<br />") ;
}

$week_list = array();
while ($row = mysql_fetch_array($result))
{
    $week_list[] = $row;
}

// Run the years
for ($i = 0; $i < count($year_list); $i++)
{
    $on_year = $year_list[$i]['year'];
    echo $on_year;
    if (isset($_GET['year']) && $_GET['year'] == $on_year)
    {
        for ($j = 0; $j < count($week_list); $j++)
        {
            if ($week_list[$j]['year'] == $on_year)
            {
                echo $week_list[$j]['week'];
            }
        }
    }
}
Doing it this way hits the DB twice, instead of however many times it needs to while running a while loop. It also allows you to set vars that may be useful later in the script. Hope it helps.