[RESOLVED] quering for and return results from 2 tables

[esiason14]

Ok, this is driving me crazy...something seemly simple. What I want to do is pull the following info from the 2 tables below and display them one per line in table:

picture_loc, lname, fname, rush_yd

I want to pull the 10 highest values out for each week:

HEL P PLEASE 🙂

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

[raspberryh]

I think something like this:

select * from playerstats inner join players on playerstats.player_id = players.player_id order by week desc limit(0,10)

I haven't done inner join in awhile so I'm not sure that the syntax is perfect but I'm pretty sure this is the general idea of what you need.

[esiason14]

Thanks,...Ok, Im getting closer to what I want:
So far I have this:

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

//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)) {

	$picture_loc = $row['picture_loc'];
	$lname = $row['lname'];
	$fname = $row['fname'];
	$rush_yd = $row['rush_yd'];
	$week = $row['week'];

	echo "<p>" ." ".$fname." ".$lname." ".$rush_yd."</p>";
	//$i++;
	}
   }

   ?>

and this returns this:

Player Rushing Yards (Week 3 only)

What I want to do is return the current weeks rushing yards...so it has to be a distinct week...then have links back so you can check week 1 and week 2 stats as well....something like this:

(Navigation here) Week 1 Week 2 Week 3

Player names Rushing Yards

Any more help you can offer

[raspberryh]

Something like this:

<?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, you can print them out whenever you want
    $links_html .= "<a href='blah.php?week=" . $week["week"] . "'>Week " . $week["week"] . "</a>";
  }

  // print links here if you want
  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 = " . $_REQUEST["week"] . " 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)) {

    $picture_loc = $row['picture_loc'];
    $lname = $row['lname'];
    $fname = $row['fname'];
    $rush_yd = $row['rush_yd'];
    $week = $row['week'];

    echo "<p>" ." ".$fname." ".$lname." ".$rush_yd."</p>";
    //$i++;
    }
   }

   ?> 

[esiason14]

I think its getting closer... 🙂

I tried what you had above...it printed the links and then I got this error:

Week 1 Week 2 Week 3 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '; ORDER by rush_yd DESC LIMIT 10' at line 3

[raspberryh]

do you know what $_REQUEST["week"] is when you execute?
Try printing out $query and see what you get.

[esiason14]

Hi, thanks for your help so far...I tried that and I'm getting something really weird...it just returns "resource id #103"...I have no idea what that is. I tried playing around with the query and the best I could do is return the single game leaders for the season...not by week. :queasy:

[wreed]

print the $query not the $result.

Try $_GET['week'] instead of request if you are infact getting week from the url.

[wreed]

Also if your week field in the table is a varchar or anything but an int you need to have single ticks around that field... i.e.

$query = "select picture_loc, lname, fname, rush_yd 
  from playerstats, players 
  WHERE playerstats.player_id = players.player_id AND week = '" . $_REQUEST["week"] . "' ORDER by rush_yd DESC LIMIT 10;";

[esiason14]

Ok, I tried printing out the query to see what "week" was...all that came up was

week =
it was blank, this was using _$REQUEST...i tried GET too

wreed, the week field in my table is tinyint(3)

[raspberryh]

well you are not passing $week into the page then.
when you go to the page, do something like thepage.php?week=1 or something.
also you can have something like

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

That way, by going straight to the page and not passing any value, it will show the first week by default.

Also, wreed was right about the single quotes around the field. You have to add that - I'm pretty sure your query won't work if you don't. (Sorry, that was my fault I think, the code I posted earlier)

Heather

[esiason14]

Heather and wreed,
you guys are geniuses. Indeed, it is now working. Thanks a lot for the help. I'll be back soon for more help 😉

[wreed]

Great to hear! Glad we can help.

I am an ASP coder learning PHP on a per project basis so it sometimes is a learning curve and well this website is great so far from what I have seen.

[raspberryh]

Yeah I'm glad we could help too 🙂
Glad you got it working!

[esiason14]

Ok, well I'm brining this thread back to life...one more problem with this...

Here's my query again..

 $query = "SELECT lname, fname, rush_yd, player_id
  FROM playerstats, players
  WHERE playerstats.player_id = players.player_id AND week = '" . $week . "' ORDER by rush_yd DESC
  LIMIT 20";

Note: Without querying for the player_id field...it does work, however...

I need to use the player_id field to pass in a url...like this:

echo " <tr bgcolor=\"$bgcolor\"><td><a href=\"/football/players/".$player_id."\"> ".$fname." ".$lname."</a></td>";
echo "<td align=\"center\">".$rush_yd."</td></tr>";

So, when adding "player_id" to the query, I get this error:

Column: 'player_id' in field list is ambiguous

I'm guessing that I'm getting this error since player_id is in both, player and playerstats, tables.

Any help as to how I can use the player_id field and avoid this error?

[coreyp_1]

Just specify a table to select player_id from:

 $query = "SELECT lname, fname, rush_yd, playerstats.player_id
  FROM playerstats, players
  WHERE playerstats.player_id = players.player_id
  AND week = '" . $week . "'
  ORDER by rush_yd DESC
  LIMIT 20";

[esiason14]

Yep, that got read of the errror, but I cant get the correct player_id to assigned to each queried player: (a player_id is being populated in the url, just not the correct one)

<?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='weeklyleaders.php?week=" . $week["week"] . "'> Week " . $week["week"] . ""."</a>";
 }

  // print links here
  echo "<p align=\"center\">$links_html</p>";

  // now select for this specific week

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

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

 $query = "SELECT lname, fname, rush_yd, playerstats.player_id
  FROM playerstats, players
  WHERE playerstats.player_id = players.player_id
  AND week = '" . $week . "'
  ORDER by rush_yd DESC
  LIMIT 20";

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

<table align="left" width="25%">
<tr align="center"><td><th>Running Backs</th></td></tr>

<tr>
     <th><b><u>Player</u></b></th>
     <th><b><u>Rushing YDS</b></u></th>
</tr>

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

    $lname = $row['lname'];
    $fname = $row['fname'];
    $rush_yd = $row['rush_yd'];
    $week = $row['week'];
$nflteam_city = $row['nflteam_city'];
$player_id = $row['player_id'];


  if($i++ % 2) {
        $bgcolor = "silver";
    } else {
        $bgcolor = "white";
}
echo " <tr bgcolor=\"$bgcolor\"><td width=\"100%\"><a href='players.php?player=" . $player_id["player_id"] . "'> ".$fname." 

".$lname."".$nflteam_city."</td>";
echo "<td align=\"center\">".$rush_yd."</td></tr>";

        }
   }

   ?>


</table>

See anything?

[raspberryh]

I think instead of this

<a href='players.php?player=" . $player_id["player_id"] . "'>

You should be putting this

<a href='players.php?player=" . $player_id . "'>

Also I'm not sure but I think you may have to put select ..., playerstats.player_id as player_id. But I am not sure, I could be wrong - it may work fine the way you have it.

[esiason14]

ok, I'm a dummy...Thanks