如果两个表都不常见，请加入drops变量

Question

I have two tables and the commonality is the tid. the $tid is the primary key for tourneys and if it is in team_tourney then the team selected it. The code below echos everything if a team selected it but drops only the variable $tid for every non selected item. How would you suggest I work around my delema.

tables
    team_tourneys
    tourneys

my sql is

$myChoice=@mysql_query("SELECT * 
    FROM  tourneys
    LEFT JOIN team_tourneys
    ON tourneys.tid = team_tourneys.tid
    WHERE  tourney_state = '$region' AND tourney_start_date >= now()
    GROUP BY tourneys.tid ORDER BY tourney_start_date ASC
"); 

if (!$myChoice)
{ 
    die('<p>Error fetching Tourney details: ' . 
    mysql_error() . '</p>'); 
} 

while ($choice = mysql_fetch_array($myChoice))
{
    $tid = $choice['tid'];
    $tourney_name = $choice['tourney_name'];
    echo '<a href="http://www.mydomain.com/tournament-local.php?&tid='.$tid.'" class="red">'.$tourney_name.'</a></div>'
}

Answer 1

Since you are fetching into an associative array you only have one slot for any selected column name (or alias). In other words, you can not have:

array(
    "tid" => 1,
    "tid" => 2,
);

Your query is going to return all columns of all referenced tables in the order the tables are listed. Since the both tables contain tid and team_tourneys is the last table referenced, $choice["tid"] will contain team_tourneys.tid which of course can be NULL due to the LEFT JOIN.

Probably the easiest way to resolve this is to read the tourneys table last:

SELECT team_tourneys.*, tourneys.*
FROM tourneys
LEFT JOIN team_tourneys
ON tourneys.tid = team_tourneys.tid
WHERE tourney_state = '$region' AND tourney_start_date >= now()
GROUP BY tourneys.tid ORDER BY tourney_start_date ASC;

You should really enter each column name individually, however. It's bad practice to use *.