Need Help with PHP Query

o0Loco0o · September 18, 2012, 4:41am

I have 3 tables, and what I am trying to do is get this to output
just the characters.name and appropriate guild.name for that character.

Table: characters
Columns: guid, name

Table: guild_member
Columns: guid, guildid

Table: guild
Columns: guildid, name

I have tried this query:

SELECT characters.name, guild.name
FROM characters, guild
WHERE guild_member.guid = characters.guid AND guild_member.guildid = guild.guildid

But it did not work… =(

Please Help!

Thank you in advance!

red_wolf · September 18, 2012, 6:02am

Try This:
[php]
SELECT characters.name, guild.name
FROM characters INNER JOIN guild
ON guild_member.guid = characters.guid
WHERE guild_member.guildid = guild.guildid
[/php]

o0Loco0o · September 18, 2012, 6:13am

This is my code…

<?php

include ("../includes/config.php");

mysql_select_db($chardb_name, $chardb_connect) or die(mysql_error());

$query = "SELECT characters.name, guild.name
FROM characters INNER JOIN guild
ON guild_member.guid = characters.guid
WHERE guild_member.guildid = guild.guildid";

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
	echo $row['characters.name']. " - ". $row['guild.name'];
	echo "<br />";
}
?>

When I try to use it, I get this error…

Unknown column 'guild_member.guildid' in 'where clause'

Which makes no sense because I can look right at the guild_member table and see the column guildid

o0Loco0o · September 18, 2012, 6:42am

So I have no done this…

<?php

include ("../includes/config.php");

mysql_select_db($chardb_name, $chardb_connect) or die(mysql_error());

$query = "SELECT characters.name, guild.name
          FROM characters AS characters
		  LEFT JOIN guild_member AS guild_member
          ON guild_member.guid = characters.guid
		  LEFT JOIN guild AS guild
		  ON guild.guildid = guild_member.guildid
		  WHERE guild.guildid = guild_member.guildid AND guild_member.guid = characters.guid";

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['characters.name']. " - ". $row['guild.name'];
echo "<br />";
}
?>

and I just get the dashes that are supposed to be inbetween the echo’d results. It looks like there are the correct ammount of results…

malasho · September 18, 2012, 5:25pm

Hi oOLocoOo,

It looks to me like you are creating two columns with the name ‘name.’ I believe your query could be cleaned up considerably, but lets try to get things working first…

See what happens with the following:[php]include ("…/includes/config.php");

mysql_select_db($chardb_name, $chardb_connect) or die(mysql_error());

$query = “SELECT characters.name AS cname, guild.name AS gname
FROM characters AS characters
LEFT JOIN guild_member AS guild_member
ON guild_member.guid = characters.guid
LEFT JOIN guild AS guild
ON guild.guildid = guild_member.guildid
WHERE guild.guildid = guild_member.guildid AND guild_member.guid = characters.guid”;

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row[‘cname’]. " - ". $row[‘gname’];
echo “
”;
}[/php]

o0Loco0o · September 19, 2012, 12:15am

I ended up getting this to work with this query…

SELECT characters.name AS cname, guild.name AS gname FROM characters AS characters LEFT JOIN guild_member AS guild_member ON guild_member.guid = characters.guid LEFT JOIN guild AS guild ON guild.guildid = guild_member.guildid WHERE guild_member.guid = characters.guid

Thank you guys for your help!

o0Loco0o · September 19, 2012, 3:10am

ok, new problem I have it displaying the name and guild of each character, but now i need to be able to get the guildid for each character. Here is the code I am using, it is returning a black result for my guild_member.guildid query

<?php

include ("../includes/config.php");

mysql_select_db($chardb_name, $chardb_connect) or die(mysql_error());

$query = "SELECT characters.name AS cname, guild.name AS gname, guild_member.guildid AS gid
FROM characters AS characters
LEFT JOIN guild_member AS guild_member ON guild_member.guid = characters.guid
LEFT JOIN guild AS guild ON guild.guildid = guild_member.guildid
WHERE guild_member.guid = characters.guid";

$result = mysql_query($query) or die(mysql_error());

?>
<table align=center cellpadding="1" cellspacing="1"  border="1">
<thead>
<td>Name</td>
<td>Guild</td>
</thead>
<tbody>
<?php
while($row = mysql_fetch_object($result))
{
$char_name = $row->cname;
$guild_name = $row->gname;
$guild_id = $row_>gid;

echo "<tr>";
echo "<td>",$char_name,"</td>";
echo "<td><a href='characters_guild.php?guild_id=",$guild_id,"'>",$guild_name,"</a></td>";
echo "</tr>";
}
?>

o0Loco0o · September 19, 2012, 4:28am

Got it, it was a typo at

$guild_id = $row_>gid;

should be

$guild_id = $row->gid;