Join in alphabetical mysql query

[Badfish]

My first post here and not sure if this should be in the php or mysql forum?

Im also not very competent with mysql & php, I just sort of learn as I go by pulling codes apart and combining pieces together from various scripts until it works :thumbsup:

What Im doing at the moment is creating an add on database for my xmb forum for a sports website and Im creating a list of players with their profiles etc.
Ive acheived the list so far with the scrip below.

--------------------------------------------------------------------------

<?
$link = "$PHP_SELF?alphabet";

echo "<center><a href='$link=a'>A</a> | <a href='$link=b'>B</a> | <a href='$link=c'>C</a> | <a href='$link=d'>D</a> | <a href='$link=e'>E</a> | <a href='$link=f'>F</a> | <a href='$link=g'>G</a> | <a href='$link=h'>H</a> | <a href='$link=i'>I</a> | <a href='$link=j'>J</a> | <a href='$link=k'>K</a> | <a href='$link=l'>L</a> | <a href='$link=m'>M</a> | <a href='$link=n'>N</a> | <a href='$link=o'>O</a> | <a href='$link=p'>P</a> | <a href='$link=q'>Q</a> | <a href='$link=r'>R</a> | <a href='$link=s'>S</a> | <a href='$link=t'>T</a> | <a href='$link=u'>U</a> | <a href='$link=v'>V</a> | <a href='$link=w'>W</a> | <a href='$link=x'>X</a> | <a href='$link=y'>Y</a> | <a href='$link=z'>Z</a> |<br />" ;

?>
<br />
<?

$result = mysql_query("SELECT * FROM sanfl_player_profile WHERE LEFT(surname,1) = '$alphabet' ORDER BY surname");

echo "<table class=mediumtxt border=1 style=border-collapse:collapse cellspacing=$borderwidth cellpadding=$tablespace bordercolor=$bordercolor>";
echo "<tr><td width=20%><strong>Club</strong></td><td width=20%><strong>career</strong></td><td width=20%><center><strong>Details</strong></center></td><td width=20%><center><strong>Edit</strong></center></td><td width=20%><center><strong>Delete</strong></center></td></tr>";

$resultsnumber = mysql_numrows($result);
$alternate = "2";
#echo "Number of results: $resultsnumber";
while ($row = mysql_fetch_array($result)) {

$player_id = $row["player_id"];
$surname = $row["surname"];
$givenname = $row["givenname"];
$career = $row["career"];

if ($alternate == "1") {
$color = "#ffffff";
$alternate = "2";
}
else {
$color = "#efefef";
$alternate = "1";
}

echo "<tr bgcolor=$altbg1><td width=20%>$surname, $givenname</td><td width=20%>$career</td><td width=20%><a href='sanfl_player_profile.php?player_id=$player_id'>see details</a></td><td width=20%><a href='edit_sanfl_player.php?player_id=$player_id'>edit record</a></td><td width=20%><a href='delete_sanfl_player.php?player_id=$player_id' onClick=\"return confirm('Are you sure?')\">delete record</a></td></tr>";

}
if ($resultsnumber == "0") {
echo "<center>Sorry - no entries under this letter</center>";
}
echo "<br /></table>";
?>

--------------------------------------------------------------------------

As you can see this draws from a table named sanfl_player_profile & displays the fields surname, given name.
What I need help with is to create a INNER JOIN ? if possible to a table named sanfl_player_career which also has a field player_id which is the relationship between these two tables, the sanfl_player_career table also has a field named club which I want to search for in the query as some players have played for different clubs and i want to be able to add to the original select query to also join and search the sanfl_player_career_table in the field club for any player with the club id of 5 or 6.

Is this possible at all as I have tried it in various ways and always get errors from the line below

$result = mysql_query("SELECT * FROM sanfl_player_profile WHERE LEFT(surname,1) = '$alphabet' ORDER BY surname");


Any help gratefully appreciated.

[punkpuke]

Hey, I suck at SQL and I would just run another query for every loop through or something. So, if you figure that out, e-mail me. =)

But, I just wanted to say that your query has a security whole and that "Alphabet Link Thing", might look better in a loop with an array. Plus an eregi function to check the inputted $alphabet so your site doesn't get SQL injected.


<?
$link = $PHP_SELF."?alphabet";
$alphabet_array = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X ','Y','Z');

echo '<center> | ';
for($i = 0 ; $i < count($alphabet_array); $i++)
{
echo '<a href='.$link.'='.strtolower($alphabet_array[$i]).'>'.$alphabet_array[$i].'</a> | ';
}

if(!eregi("^[A-Z]*$", $alphabet))//IF Alpha Only
{
$alphabet = 'a';//Sets Letter to "A" if inputted letter is invalid
}

if(!get_magic_quotes_gpc())
{
$alphabet = addslashes($alphabet);
}

echo '<br /><br />';
//MORE PHP after...
?>