left join

[nielsho]

Hello, u might say take a look around first on this subject, but i have done for a while and cant figure out the problem.

I got 3 tables:

Table 1 (results) contains: challengeid, clanid
Table 2 (challenges) contains: challengeid, clanid and challengedclanid
Talbe 3 (clans) contains: clanid, clanname

At the moment i got my code kinda working... It displays the rows from results. in other words.... it displays the names of the clans sorted by challengeid.

My problem is, it won't display the challengedclan name.

Here is my code:

<?php 
include("league_system/config.php");
?>
<?php

MYSQL_CONNECT($master_databasehost,$master_username,$master_password) OR DIE("Unable to connect to database"); 

@mysql_select_db("$master_database") or die("Unable to select database"); 

$query = "SELECT * FROM clans LEFT JOIN results ON clans.clanid=results.clanid LEFT JOIN challenges ON results.challengeid=challenges.challengeid=challenges.challengedclanid WHERE clanname LIKE '%$clanname%'";


$query_result_handle = mysql_query ($query) 
or die ('<h6><center>The requested table or database does not exist</center></h6>'); 

$num_of_rows = mysql_num_rows ($query_result_handle) 
or die ("<center><h6>There are no records in the database at this time</h6></center>"); 

for ($count = 1; $row = mysql_fetch_row ($query_result_handle); ++$count) 
{ 



print "<table border = 0 valign=center width=100%> 
"; 
print"<tr bgcolor=\"#c0c0c0\"><td width=49%><center>$row[1]</center> </td><td width=2%><center>defeated</center></td><td width=49%><center>$row[2]</center></td> 
"; 
print"
</tr> 
"; 
print "<td> <center>Score</center></td> 
"; 
print "<td> <center>&nbsp;</center> </td> 
"; 
print "<td> <center>&nbsp;</center> </td> 
"; 
print "</tr></table><br><br> 
"; 

}

 

Can any1 plz point me in the right direction. Tnx in advance

[The_Chancer]

Youa re not making reference to the challengedclanid in the select clause...

You can use select clans.clanid instead of using the FROM part...

That may help...

[nielsho]

call me stupid, but tried it in several versions but still doesn't work.. Can u tell me how to call that challengedclanid then?

[nielsho]

if it may clear up some things...... here are my 3 tables

CREATE TABLE challenges (
challengeid mediumint(9) NOT NULL auto_increment,
clanid mediumint(9) NOT NULL default '0',
challengedclanid mediumint(9) NOT NULL default '0',
leagueid mediumint(9) NOT NULL default '0',
confirmed tinyint(4) NOT NULL default '0',
cancelled tinyint(4) NOT NULL default '0',
completed tinyint(4) NOT NULL default '0',
PRIMARY KEY (challengeid)
) TYPE=MyISAM AUTO_INCREMENT=66 ;

CREATE TABLE clans (
clanid mediumint(9) NOT NULL auto_increment,
clanname varchar(50) NOT NULL default '(name me)',
clantag varchar(10) default NULL,
homepage varchar(100) default NULL,
logo varchar(100) default NULL,
status tinyint(4) NOT NULL default '0',
PRIMARY KEY (clanid)
) TYPE=MyISAM AUTO_INCREMENT=38 ;

CREATE TABLE results (
resultid bigint(20) NOT NULL auto_increment,
resultgroupid bigint(20) NOT NULL default '0',
leagueid mediumint(9) NOT NULL default '0',
challengeid mediumint(9) NOT NULL default '0',
clanid mediumint(9) NOT NULL default '0',
mapid mediumint(9) NOT NULL default '0',
goalscore bigint(20) default NULL,
fragscore bigint(20) default NULL,
won tinyint(4) NOT NULL default '0',
lost tinyint(4) NOT NULL default '0',
drew tinyint(4) NOT NULL default '0',
confirmed tinyint(4) NOT NULL default '0',
matchcomment varchar(255) NOT NULL default '0',
PRIMARY KEY (resultid)
) TYPE=MyISAM AUTO_INCREMENT=67 ;

hope u can point me to correct direction