I'm a noob and am struggling with what should be some fairly straightforward php and mysql coding. I have a page that I am building and am able to get the following code to work:

<?php
include 'includes/config.php';
include 'includes/opendb.php';
?>
<div align="center"><h1>
<?php

$id=$_GET['player_id'];
?>
<?
$query=
"SELECT 	p.player_id
			, p.player_name
			, p.team_id
			, bbs_team.team_name
			, race
			, position
			, player_status			
			
			
FROM 		bbs_player	p

JOIN		bbs_team
ON			p.team_id = bbs_team.team_id


WHERE		player_id = '$id'

";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
$race = $row['race'] ; 
$team_name = $row['team_name'] ; 
$position = $row['position'] ; 
$player_status = $row['player_status'] ; 

  $display_block .= "<tr><td>$team_name</td>
  <td>$race</a></td>
  <td>$position</td>
  <td>$player_status</td>
 " ; 
print $row['player_name'];
	?>
	</h1></div>
	<table width="45%"  border="0" cellspacing="0" align="left">
	<tr> 
<th align="left" scope="row"><strong>Team</strong></th>
<th align="left" scope="row"><strong>Race</strong></th> 
<th align="left" scope="row"><strong>Position</strong></th> 
<th align="left" scope="row"><strong>Status</strong></th> 
</tr>
	<?
echo "$display_block" ; 
?>
</table>

	
<?
include 'includes/closedb.php';
?>

However, when I try to add a join onto another table using this code it doesn't work and no database result shows up at all including the stuff that works above:

<?php
include 'includes/config.php';
include 'includes/opendb.php';
?>
<div align="center"><h1>
<?php

$id=$_GET['player_id'];
?>
<?
$query=
"SELECT 	p.player_id
			, p.player_name
			, p.team_id
			, bbs_team.team_name
			, race
			, position
			, player_status			
			, SUM(pgs.star_player_points) AS totalspps
			
			
FROM 		bbs_player	p

JOIN		bbs_team
ON			p.team_id = bbs_team.team_id

JOIN		bbs_player_game_stat pgs
ON			p.player_id = pgs.player_id


WHERE		player_id = '$id'

";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
$race = $row['race'] ; 
$team_name = $row['team_name'] ; 
$position = $row['position'] ; 
$player_status = $row['player_status'] ; 

  $display_block .= "<tr><td>$team_name</td>
  <td>$race</a></td>
  <td>$position</td>
  <td>$player_status</td>
 " ; 
print $row['player_name'];
	?>
	</h1></div>
	<table width="45%"  border="0" cellspacing="0" align="left">
	<tr> 
<th align="left" scope="row"><strong>Team</strong></th>
<th align="left" scope="row"><strong>Race</strong></th> 
<th align="left" scope="row"><strong>Position</strong></th> 
<th align="left" scope="row"><strong>Status</strong></th> 
</tr>
	<?
echo "$display_block" ; 
?>
</table>

	
<?
include 'includes/closedb.php';
?>

I know that this the pgs table works because I have another page elsewhere that successfully joins on exactly the same player_id = player_id. Any suggestions?

It is better to post only the queries in question instead of posting the whole PHP code.

I don't appear to be able to edit because there is a reply. In any case I kind of found a way round my problem. First up I learnt that it is useful to have error syntax in the right places. The problem I had was with ambiguity so all I had to do make an initial fix was to define the table properly in the WHERE clause.

However, this did not give me any results so my eventual fix was just to split the query into two and put stuff I wanted from the second join into the second query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.