Ok, this is driving me crazy... What I want to do is pull the following info from the 2 tables below and display
week
picture_loc, fname, lname, rush_yd,
playerstats Table:
stat_id
player_id
year
week
nflteam_id
pass_att
pass_cmp
pass_yd
pass_td
rush_att
rush_yd
rush_td
players table:
player_id
lname
fname
picture_loc
nflteam_id
position_id
MY goal is this...
1. have a display for each week showing the 10 players with the most rushing yyards for the week, creating links for the previous weeks..something like this:
Code:
Week 1 Week 2 Week 3
Code:
Player Rushing Yards
picture_loc fname lname rush_yd
2. I also would like to query for the season leaders showing the players with the most rushing yards for the season...adding each players stats for the year, with a similar display as above...
For #1, I have the follow so far: Although this query only returns the players with the most single game rushing yards in the season...not by week and not total. Also, in the second query, I dont want the actual week # specified. I want something like week = " . $_REQUEST["week"] . " but that doesnt seem to be working...Can anyone help me with this one?
PHP Code:
<?php
$public=1;
require_once("config.php");
require_once($DOC_ROOT . "/lib/header.php");
// select weeks first, and make links
// there's probably a better way to do this query but this
// is all I could think of!!
$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='players.php?week=" . $week["week"] . "'>Week " . $week["week"] . ""."</a>";
}
// print links here
echo $links_html;
// now select for this specific week
//query
$query = "SELECT picture_loc, lname, fname, rush_yd
FROM playerstats, players
WHERE playerstats.player_id = players.player_id AND week= 3
ORDER by rush_yd DESC
LIMIT 10";
$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
{
while($row = MySQL_fetch_array($result)) {
$lname = $row['lname'];
$fname = $row['fname'];
$rush_yd = $row['rush_yd'];
$week = $row['week'];
$picture_loc = $row['picture_loc'];
echo "<table>";
echo "<tr>";
echo "<td align=\"left\">" ." ".$picture_loc."".$fname." ".$lname."</td>";
echo "<td align=\"right\">".$rush_yd."</td>";
echo "</tr>";
echo "</table>";
}
}
?>
dBforums
>
Complex query help..at least to me
09-30-05, 00:24
Linear Mode
