php help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
esiason14
Forum Newbie
Posts: 10
Joined: Sun Jul 17, 2005 12:19 pm

php help

Post 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>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look into GROUP BY syntax for your SQL..

And try to come up with a bit more descriptive topic titles.. ;)
esiason14
Forum Newbie
Posts: 10
Joined: Sun Jul 17, 2005 12:19 pm

Post 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 :(
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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).
esiason14
Forum Newbie
Posts: 10
Joined: Sun Jul 17, 2005 12:19 pm

Post 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:
esiason14
Forum Newbie
Posts: 10
Joined: Sun Jul 17, 2005 12:19 pm

Post 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:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply