Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

[PHP] Inner join

Elite Diviner
Joined
Aug 15, 2008
Messages
489
Reaction score
43
I've got an issue which I've been trying to solve, but my PHP knowledge isn't enough to solve it.

I have got 2 tables. banhammer_bans and banhammer_players.

Table banhammer_bans has this structure.
id, created_at, reason, expires_at, state, player_id, creator_id

Table banhammer_players has this structure.
id, name

So my issue is, that I want to be able to have a list on my website of the banned players, in the format "Username, Banned by, Ban date, Unban date, Reason".

So I'd have to somehow link player_id and creator_id with the id found in banhammer_players.

Here is something I've been trying, which I learned from the previous time I asked for something, but figured out it wouldn't work out so well.

Code:
SELECT * FROM `banhammer_bans` INNER JOIN `banhammer_players` ON banhammer_bans.creator_id = banhammer_bans.id ORDER BY created_at DESC

Then I simply tried echoing the names and etc, but what I got was Username and Banned by having the exact same name.
 
Experienced Elementalist
Joined
Dec 15, 2006
Messages
246
Reaction score
32
Btw, its not php fault. Its ur mysql syntax...

Try some thing like this

Select banhammer_bans.username as username_bans, banhammer_playrs.username_players inner join blablabla......(complete it as necessery)...

2
 
Elite Diviner
Joined
Aug 15, 2008
Messages
489
Reaction score
43
I know it's not php's fault.
The example you gave confuses me, but I tried something like this.
Code:
SELECT banhammer_bans.player_id, banhammer_bans.creator_id as banhammer_players.name INNER JOIN `banhammer_bans` banhammer_bans.player_id, banhammer_bans.creator_id = banhammer_players.id ORDER BY created_at DESC
But no help. I'm not sure if I completely understood what you meant with that.
 
Ginger by design.
Loyal Member
Joined
Feb 15, 2007
Messages
2,340
Reaction score
653
I haven't used SQL for years but here's my answer:

PHP:
SELECT bu.name, ba.name, bb.created_at, bb.expires_at, bb.reason
FROM banhammer_players AS bu
    JOIN banhammer_bans AS bb
    ON bu.id = bb.player_id
    JOIN banhammer_players AS ba
    ON bb.creator_id = ba.id
 
Joined
Jun 8, 2007
Messages
1,985
Reaction score
490
I haven't used SQL for years but here's my answer:

PHP:
SELECT bu.name, ba.name, bb.created_at, bb.expires_at, bb.reason
FROM banhammer_players AS bu
    JOIN banhammer_bans AS bb
    ON bu.id = bb.player_id
    JOIN banhammer_players AS ba
    ON bb.creator_id = ba.id
Looks good 'nuff to me.
 
Elite Diviner
Joined
Aug 15, 2008
Messages
489
Reaction score
43
Thanks. The syntax you gave works great. My only issue is echoing them now.
I tried to echo .$row['bu.name']. but I keep getting a blank result.

Here is the full code.

Code:
	$sql = "SELECT bu.name, ba.name, bb.created_at, bb.expires_at, bb.reason FROM banhammer_players AS bu JOIN banhammer_bans AS bb ON bu.id = bb.player_id JOIN banhammer_players AS ba ON bb.creator_id = ba.id ORDER BY bb.created_at DESC LIMIT $offset, $rowsperpage";
	$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

			$c = 0;

			echo '<table width="100%" cellspacing="0" cellpadding="5">';
			echo '<tr class="';
			echo $c++&1 ? 'odd' : 'even';
			echo '">';
			echo '<th style="text-align: left;">Avatar</th>';
			echo '<th style="text-align: left;">Username</th>';
			echo '<th style="text-align: left;">Banned by</th>';
			echo '<th style="text-align: left;">Ban date</th>';
			echo '<th style="text-align: left;">Unban date</th>';
			echo '<th style="text-align: left;">Reason</th>';
			echo '</tr>';

	// while there are rows to be fetched...
	while ($row = mysql_fetch_assoc($result)) {
	
			echo "<tr class='";
			echo $c++&1 ? 'odd' : 'even';
			echo "'>";
			echo "<td><img src='http://minotar.net/avatar/".$row['bu.name']."/30' /></td>";
			echo "<td>".$row['bu.name']."</td>";
			echo "<td>".$row['ba.name']."</td>";
			echo "<td>".$row['bb.created_at']."</td>";
			
				if(''.$row['bb.expires_at'].'' == 0)
					echo "<td>Permanent</td>";
				else
					echo "<td>".$row['bb.expires_at']."</td>";
			
			echo "<td>".$row['bb.reason']."</td>";
			echo "</tr>";

	} // end while
 
Last edited:
Joined
Jun 8, 2007
Messages
1,985
Reaction score
490
Just dump $row and see what you get,
PHP:
while (/*set rows... */) {
    var_dump($row);
}

PHP offers an enormous set of broken tools for you to use at will~ learn to use them ;)
 
Elite Diviner
Joined
Aug 15, 2008
Messages
489
Reaction score
43
Now I get a huge list of results. However, I can see that it is completely missing the name of the banned player.

The "name" tells the name of the creator of the ban.
Code:
array(4) { ["name"]=> string(6) "Sibens" ["created_at"]=> string(19) "2012-09-05 08:18:27" ["expires_at"]=> NULL ["reason"]=> string(25) "Stealing stuff on a chest" } array(4) { ["name"]=> string(7) "PipeCat" ["created_at"]=> string(19) "2012-08-18 18:35:03" ["expires_at"]=> string(19) "2012-08-19 18:35:03" ["reason"]=> string(76) "Didn't stop arguing with other players after being told to stop by Moderator" } array(4) { ["name"]=> string(7) "PipeCat" ["created_at"]=> string(19) "2012-08-18 18:34:44" ["expires_at"]=> string(19) "2012-08-19 18:34:44" ["reason"]=> string(76) "Didn't stop arguing with other players after being told to stop by Moderator" }
 
Joined
Jun 8, 2007
Messages
1,985
Reaction score
490
That means the query isn't selecting the banned player- is that all you want to select? Which column in which table holds the banned player's name?

PS, you are using an HTML client for the PHP script so the results' line breaks are ignored. To fix the spacing use <pre> tags or test PHP using the command line.

PHP:
echo('<pre>');
var_dump($variable_to_dump);
die('</pre>');

Knowing how to test your scripts is more important than knowing how to code the scripts.
 
Last edited:
Elite Diviner
Joined
Aug 15, 2008
Messages
489
Reaction score
43
What I want to select, is banned player, creator of ban, ban date, unban date and reason.
The table banhammer_players has the player id as well as player name.

Here is the structure of banhammer_bans.
bans - [PHP] Inner join - RaGEZONE Forums

And structure of banhammer_players.
players - [PHP] Inner join - RaGEZONE Forums

Basically player_id and creator_id had to be linked to the id.

When I type the syntax into phpMyAdmin, it gives the following results.
syntax - [PHP] Inner join - RaGEZONE Forums

So apparently it has duplicate result of name which then makes it fail to echo it?
 

Attachments

You must be registered for see attachments list
Joined
Jun 8, 2007
Messages
1,985
Reaction score
490
PHP:
SELECT bu.name AS banned_player, ba.name AS ban_creator, bb.created_at, bb.expires_at, bb.reason
    FROM banhammer_players AS bu
    JOIN banhammer_bans AS bb
    ON bu.id = bb.player_id
    JOIN banhammer_players AS ba
    ON bb.creator_id = ba.id

You can use the 'AS' operator to disambiguate between the identical field names by providing a custom alias name.
 
Last edited:
Back
Top