Joins and displaying table data

Martsparts

Hello again chaps,

I'm struggling here. Basically I have three tables: Players, Managers and team_players.

team_players has the Managers_ID (foreign key) and Players_ID (foreign key) and its ID (primary key). Now I'm trying to display the row of information on the team_player table.

So it would read: 1 (ID) Bob (Managers_ID) Rooney, Man Utd, Striker (Players_ID).

But so far I'm getting nothing 🙁

Below is the code and help would be most appreciated

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?PHP

$user_name = "nuts";
$password = "nutst";
$database = "nuts";
$server = "nuts";

mysql_connect($server, $user_name, $password) or die ('Error: ' . mysqlerror());
mysql_select_db($database);

$query="SELECT Managers_ID, Players_ID
FROM team_players
LEFT JOIN Managers
ON team_players.Managers_ID=Managers.ID
LEFT JOIN Players
ON team_players.Players_ID=Players.ID";

$result = mysql_query($query) or die ('Error updating database');

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['Managers.ID']. " - ". $row['Players_ID'];
echo "<br />";

</body>
</html>

Emma

Firstly, I assume your not getting any error messages from the die functions used.

Check the SQL actually works; does your select statement return the correct results when you run it in the database? If it does then echo out $row without the [] part and see what you get.

johanafm

Martsparts;10985702 wrote:
Below is the code and help would be most appreciated

All code should be within its proper bbcode tags, i.e. php, html or code tags.

Post only relevant code. For example, the doctype has nothing to do with the matter, so that obviously doesn't need to be included. If you're not sure what code is relevant to post or not, try reducing the code until you have the minimum required to reproduce the error. And by doing so, you will often be able to find it yourself, since you may actually be down to a few lines of code.

Martsparts;10985702 wrote:
But so far I'm getting nothing

You should be getting at least one error. If you don't, turn on error reporting. Read the php documentation about php.ini, and see error_reporting, log_errors, display_errors.

Martsparts;10985702 wrote:
[/code]
$query="SELECT Managers_ID, Players_ID
$result = mysql_query($query) or die ('Error updating database');
[/code]

You are issuing a SELECT query, which will never update the database, so this error message is highly missleading. Moreover, you don't log the actual error, meaning you have absolutely no useful information to resolve the issue should there ever be an error.

Martsparts;10985702 wrote:
[/code]
while($row = mysql_fetch_array($result)){
echo $row['Managers.ID']. " - ". $row['Players_ID'];
echo "<br />";
?>
[/code]

Since you do not select any field called Managers.IDor alias one with that name, the array element $row['Managers.ID'] does not exist, which you should get an error for on each iteration. Also, do note that even if you had

SELECT Managers.ID

you still would have no array element called 'Mangers.ID'. It would be called 'ID'.

More importantly at the present however, is the only error message which would have shown up at this point: "Parse error: syntax error, unexpected $end" (or whatever the actual message is), since you are lacking a closing } for your while loop, opening and closing brackets must ALWAYS match up, which means that unless they do, there should be no end of the php script.