Select data from joined table

[kee1108]

I have two tables, one has all the information, and the other one has all the row of a particular status.  I tried to join them and then select from the result of the join.  How should I do it?  I am very new to php.

 

Or is there a better way to do this.  Table 1 has column (ID, Names, Contract_Left, etc), Table 2 has column (ID, RFA).  Basically I just want the names with a contract left of 1 year who isn't a RFA.  Table 2 only contains IDs for those who is RFA.

 

<?php
// Make a MySQL Connection
mysql_connect("localhost", "USERNAME", "PW") or die(mysql_error());
mysql_select_db("rzca-football_rzl") or die(mysql_error());

// Get specific results from the database
$query = "SELECT * FROM players LEFT JOIN rfa ON players.PGID = rfa.PGID UNION
			SELECT * FROM players RIGHT JOIN rfa ON players.PGID = rfa.PGID WHERE players.PGID IS NULL
			WHERE players.Contract_Left = '1' ORDER BY players.Team";
$result = mysql_query($query) or die(mysql_error());
?>

<div>

<table border="0" class="smallplainWithBorder" cellpadding="2" cellspacing="0" width="600">

<tr class="darkback">
<td colspan="7" valign="bottom" class="statcell"><b><h1>Pontential Free Agent In 2013</h1></b></td>
</tr>
<?php while($row = mysql_fetch_array( $result )) {
	echo "<tr class='darkback'>";
	echo "<td class=statcell>" . "<img src=http://rzl-football.com/files/images/icons/".$row['Team'].".gif>" . "</td>";
	echo "<td class=statcell>" . "<a href=http://rzl-football.com/player.php?pgid=".$row['PGID'].">" . $row['First_Name'] . " " . $row['Last_Name'] . "</a></td>";
	echo "<td class=statcell>" . $row['Pos'] . "</td>";
	echo "<td class=statcell>" . $row['Age'] . "</td>";
	echo "<td class=statcell>" . $row['HGT'] . "</td>";
	echo "<td class=statcell>" . $row['WGT'] . "</td>";
	echo "<td class=statcell>" . $row['OVR'] . "</td>";
	echo "</tr>";
	}
 ?>
</table>

</div>

 

Thank you very much in advance.  Any pointer will help.

[akitchin]

what will work better than a UNION here is a LEFT OUTER JOIN. you can pop table 2 onto the end of table 1's information and use RFA in the WHERE clause to determine if there is an entry for them there or not:

 

SELECT t1.Names AS names, t2.RFA AS RFA
  FROM table1 t1
  LEFT OUTER JOIN table2 t2
    ON t1.ID=t2.ID
  WHERE
    t1.Contract_Left=1 AND
    t2.RFA IS NULL
  ORDER BY t1.Names

 

the LEFT OUTER JOIN appends the matching row of t2 to the right-hand side of t1, and in the case that there is no matching row, it appends a series of NULL values. this helps to identify any item in t1 without a matching entry in t2.

[kee1108]

Thank you very much for the quick respond.  I did try LEFT OUTER JOIN, but I was trying to state the condition where RFA <> 'Yes' or something like that.

 

Using IS NULL seems to do the trick.  It works now.  But I run into another problem.  When I try to echo the PGID within a link, it doesn't work.  I also tried players.PGID and still it doesn't add to the link.  It was working before.

 

<?php
// Make a MySQL Connection
mysql_connect("localhost", "root", "rootadmin") or die(mysql_error());
mysql_select_db("rzca-football_rzl") or die(mysql_error());

// Get specific results from the database
$query = "SELECT * FROM players LEFT OUTER JOIN rfa ON players.PGID = rfa.PGID WHERE players.Contract_Left = '1' AND rfa.rfa IS NULL ORDER BY players.Team";
$result = mysql_query($query) or die(mysql_error());
?>

<div>

<table border="0" class="smallplainWithBorder" cellpadding="2" cellspacing="0" width="600">

<tr class="darkback">
<td colspan="7" valign="bottom" class="statcell"><b><h1>Pontential Free Agent In 2013</h1></b></td>
</tr>
<?php while($row = mysql_fetch_array( $result )) {
	echo "<tr class='darkback'>";
	echo "<td class=statcell>" . "<img src=http://rzl-football.com/files/images/icons/".$row['Team'].".gif>" . "</td>";
	echo "<td class=statcell>" . "<a href=http://rzl-football.com/player.php?pgid=".$row['PGID'].">" . $row['First_Name'] . " " . $row['Last_Name'] . "</a></td>";
	echo "<td class=statcell>" . $row['Pos'] . "</td>";
	echo "<td class=statcell>" . $row['Age'] . "</td>";
	echo "<td class=statcell>" . $row['HGT'] . "</td>";
	echo "<td class=statcell>" . $row['WGT'] . "</td>";
	echo "<td class=statcell>" . $row['OVR'] . "</td>";
	echo "</tr>";
	}
 ?>
</table>

</div>

 

 

[akitchin]

first, do you have an entry for each player in the rfa table, just with a value of either "yes" or "no"? or is there simply no entry for the player if they have no RFA? if the former, you will need to change the condition to rfa.rfa <> 'Yes', since even when they have no RFA, they will still have a value for it.

 

as for the PGID issue, it might be helpful to stipulate which table all the data is coming from:

 

SELECT players.* FROM players ...

 

see if that helps. if it doesn't, try print_r() on $row to see what data is coming out of the query - that might help you find the correct key. i would suspect it's tripping up because there is a PGID in both tables.

[kee1108]

first, do you have an entry for each player in the rfa table, just with a value of either "yes" or "no"? or is there simply no entry for the player if they have no RFA? if the former, you will need to change the condition to rfa.rfa <> 'Yes', since even when they have no RFA, they will still have a value for it.

 

as for the PGID issue, it might be helpful to stipulate which table all the data is coming from:

 

SELECT players.* FROM players ...

 

see if that helps. if it doesn't, try print_r() on $row to see what data is coming out of the query - that might help you find the correct key. i would suspect it's tripping up because there is a PGID in both tables.

 

As for the first issue, I am not even going to try to make my way to work. The way you suggested is a lot more neater .

 

The PGID is now solved as well.  THANKS.  You are the man.  I was trying to specific the table.column in the output portion.  But instead selected only from the players table in the query will work.

 

Thank you very much!!!