MySQL and help selecting multiple records in the same table... yes, I'm new!

[mxl]

Hello,

 

I'm new to both PHP and mySQL.  Can I please ask for some help with mul

 

In learning to use PHP, I've built myslef a little database with a few tables in it, Games, Teams and Venues.

 

In the Games record, I have the following fields

 

pid

home_team

away_team

venue

game_date

game_time

 

In the Teams record, I have the following fields

 

team_name

team_name_short

sport_type

 

In the Venues record, I have the following fields

 

venue

venue_address

vene_city

venue_state

venue_zip

venue_seating_chart

 

Now, I have the code that will select the Games record.  It is as follows:

 

$q_string = "SELECT * FROM games WHERE pid=".trim($pid)."";

 

This works nicely.  I can just pick off the information like this:

 

$result= mysql_query("$q_string") or die("Connect Error: ".mysql_error());

 

if (mysql_num_rows($result) == 0) //Returns the number of rows found (if any)

{

    echo 'There are no results!';

    mysql_close($conn);

    exit;

}

else

{  //$result === TRUE

  // fetch the data.  It should only come in as one row because $pid is unique.

  while($row_data = mysql_fetch_array($result))

  {

        $home_team=$row_data['home_team'];

        $away_team=$row_data['away_team'];

        $venue=$row_data['venue'];

...

 

 

This all works nicely.  Now, I think I can get the venue information, but putting that together with getting two team records from home_team and away_team in the games record, now I already feel stupid.  I think I can manage venue, but getting the games record, two teams record and the venue record all in one query has me stumped.

 

Can someone please help me understand how I can set this up? 

[fenway]

You need to join the tables together using the FKs.