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.
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 ;
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 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> </center> </td>
";
print "<td> <center> </center> </td>
";
print "</tr></table><br><br>
";
}
Can any1 plz point me in the right direction. Tnx in advance
and i know u will say where is the challengedclanid then...but that is where i got the problem...i dont know how to solve that.
Since i tried it
And this doesnt give me the correct output aswell, although clanid and challengedclanid are both linked to clanname :queasy:
$query = "SELECT * FROM clans LEFT JOIN challenges ON clans.clanid=challenges.clanid AND challenges.challengedclanid LEFT JOIN results ON challenges.challengeid=results.challengeid WHERE clanname LIKE '%$clanname%'";
basicly i want to make it so that challengeclan and challengedclan name are both displayed. So from there i can show who won lost or if they tied..
