Zymic Webmaster Forums > Selecting From A Joined Table

Full Version: Selecting From A Joined Table

Zymic Webmaster Forums > Web Design & Development > Server Side Scripting > PHP

rickysports

Aug 27 2009, 04:40 PM

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.

CODE

<?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. Only pointer will help.

Aug 27 2009, 05:42 PM

I find it best to avoid UNION's where ever possible, your query could be as simple as this:

SQL

SELECT * FROM players LEFT JOIN rfa ON players.PGID = rfa.PGID WHERE players.Contract_Left = '1' AND players.PGID IS NULL ORDER players.Team

You shouldn't use '*', instead list the actual columns you want to select.

swordz

Aug 28 2009, 11:38 PM

I'd actually only have 1 table - have a boolean for if an id is RFA or not. Otherwise you've got 2 tables each with the same primary id (although I know some id's are missing from table 2).

Swordz

NDBoost

Sep 4 2009, 01:53 PM

The other suggestion i might make is setting a set of URLs / paths in a config file to a PHP array and echoing the PHP array back. That way if the paths ever have to change you're editing one set of arrays instead of 500 lines of code..

for instance using something like this(I got this from NetTuts):

Create Constants for internal paths:

CODE

/*
     Creating constants for heavily used paths makes things a lot easier.
     ex. require_once(LIBRARY_PATH . "Paginator.php")
*/
define("LIBRARY_PATH", realpath(dirname(__FILE__) . '/v4/library'));
define("TEMPLATES_PATH", realpath(dirname(__FILE__) . '/templates'));
define("JS_PATH", '/v4/js');
define("CSS_PATH", '/v4/css');
define("LAYOUT_IMG_PATH", '/img/layout');
define("CONTENT_IMG_PATH", '/img/content');

Use the constants like so:

CODE

<?php require_once(TEMPLATES_PATH . "/quickcontact.php"); ?>

Or using arrays like so:

CODE

     "urls" => array(
         "baseUrl" => "http://yoursite.com/v4"
     ),

Echoing back array values like so:

CODE

<?php echo $config['urls']['baseUrl]'] . 'nav/services/web-design.html' ?>

Checkout this tutorial for full details:
http://net.tutsplus.com/tutorials/php/orga...-the-right-way/

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.