Multi Table Query

[jay7981]

Hello all,

I have 4 tables that i need to join and pull matching data from, First let me describe the tables and then i can explain futher the issue i am having.

The tables manage admins in an online game, and i am unable to change the table structure due to it being hardcoded into the game.  Now for the tables,

DB Name admmgr
Table 1 (Holds all members of my clan weather they have admin or not)
clan_members
     authid (primary key)(manually defined)
     name
     rank (number from 1 - 10)

Table 2 (holds the actual admins)
sm_admins
     id (primary key)(auto inc)
     authtype
     identity (same as authid in clan_mambers)
     password
     flags (usally NULL as we use the groups flags instead)
     name (same as name in clan_members so no need to call this field again)
     immunity (usally NULL as we use the groups Immunity)

Table 3 (Holds the Groups that each person in sm_admins can be assigned to)
sm_groups
     id (primary key)(auto inc)
     flags (the flags that we use to give admin permissions)
     name (name of the group)
     immunity_level

Table 4 (Holds the information of which admin is assigned to which group)
sm_admins_group
     admin_id (same as id in sm_admin)
     group_id (same as id in sm_groups)
     inherit_order

 

what i am trying to do is display each member that is in clan_member that is in each group, thier flags, which group name they are in, thier authid and thier name example to follow

 

clan_members=
authid 123456, name john, rank leader
authid 654321, name chris, rank member
authid 987456, name bob, rank NULL

sm_admins=
id 1, authtype steam, identity 123456, password NULL, flags NULL, name John, immunity NULL
id 2, authtype steam, identity, 654321, password NULL, flags NULL, name Chris, immunity NULL

sm_groups=
id 1, flags abcdefgh, name Leaders, immunity_level 10
id 2, flags gh, name Members, immunity_level 1

sm_admins_groups=
admin_id 1, group_id 1, inherit_order 0
admin_id 2, group_id 2, inherit_order 0

 

the display i am looking for is this:

Name     AuthID       Rank           Group         Flags         Immunity
John      123456      Leader       Leaders      abcdefgh        10
chris      654321      Member     Members         gh                1

 

Now i know i have to use left/right joins and i have done this before with 2 tables below is the code i used to do that and it works flawlessly, im just not sure how to edit the query to include 4 tables....

 

SELECT clan_members.authid, clan_members.rank, clan_members.name, admins.access 
FROM bioclan.clan_members 
LEFT OUTER JOIN bioclan.admins ON clan_members.name = admins.name 
WHERE clan_members.rank = 9 
ORDER BY clan_members.rank DESC, clan_members.name

 

Thank you in advance for your help!

[kickstart]

Hi

 

Not 100% sure what you want. This might do it:-

 

SELECT a.name, a.authid, a.rank, b.access, d.name AS Group, d.flags, d.Immunity_Level 
FROM bioclan.clan_members a
LEFT OUTER JOIN bioclan.admins b ON a.authid = b.identity
LEFT OUTER JOIN bioclan.sm_admins_groups c ON b.id = c.admin_id
LEFT OUTER JOIN bioclan.sm_groups d ON c.group_id = d.id
ORDER BY a.rank DESC, a.name

 

However not sure you want to use outer joins, as if you do it will bring back bob as well.

 

Also the tables suggest that an admin can belong to multiple groups, and not sure how you want to determine which group to use in the above query if someone is in multiple groups (or whether you want multiple lines for an admin, one for each group).

 

All the best

 

Keith

[jay7981]

first of all thank you,

 

i ran that query and got an error, " Unexpected symbol 'Group' " 

 

as for multiple groups for admins we wont be using more than 1 group per admin.

 

if using outer joins is going to display bob as well then i dont want that ... i just want the admins to be displayed with the correct information

[kickstart]

Hi

 

Oops. Group is a reserved word, and I put it in there as an alias for a column. That is what it is objecting to.

 

SELECT a.name, a.authid, a.rank, b.access, d.name AS GroupName, d.flags, d.Immunity_Level 
FROM bioclan.clan_members a
INNER JOIN bioclan.admins b ON a.authid = b.identity
INNER JOIN bioclan.sm_admins_groups c ON b.id = c.admin_id
INNER JOIN bioclan.sm_groups d ON c.group_id = d.id
ORDER BY a.rank DESC, a.name

 

Try that.

 

All the best

 

Keith

[jay7981]

that worked out beautifully! thanks so much!

[jay7981]

ok now i am stuck again, using the above query it displays the information correctly, however when i change it to an UPDATE: i am updating the group that they are assigned to(housed in the "sm_admins_groups" table)  and possible thier name housed in the "sm_admins" table), using the below code it is changing all the names in the $smadmgrp_table to the same name. When i do print_r($_POST); to see what info is actually being passed from the form i get

Array ( [name] => BOB [group] => 4 [admin_id] => 66 [button] => Submit )

 

the form looks like this

<?php
$db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db ($database);

$query = "SELECT b.id, a.name, a.authid, a.rank, c.group_id AS grp_id, c.admin_id AS admin_id, d.immunity_level, d.flags FROM bioclan.clan_members a INNER JOIN bioclan.sm_admins b ON a.authid = b.identity INNER JOIN bioclan.sm_admins_groups c ON b.id = c.admin_id INNER JOIN bioclan.sm_groups d ON c.group_id = d.id WHERE b.identity='" . $_POST['auth'] . "'";
$result = mysql_query($query) or die ("Cannot query table " . mysql_error());

$row = mysql_fetch_assoc($result);

	$auth = $row['authid'];
	$name = $row['name']; 
	$group = $row['grp_id'];
	$flags = $row['flags'];
	$immune = $row['immunity_level'];
	$id = $row['id'];
	$admin_id = $row['admin_id'];

mysql_free_result($result);
mysql_close($db);
?>

<form action="sm_update.php" method="post">
  <table width="100%">
    <tr>
      <td> </td>
      <td colspan="2"> </td>
    </tr>
    <tr>
      <td><div align="right">SteamID:</div></td>
      <td colspan="2"><div align="left">
          <?php echo "$auth";?>
        </div></td>
    </tr>
    <tr>
      <td><div align="right">Name:</div></td>
      <td colspan="2"><div align="left">
          <input name="name" type="text" value="<?php echo "$name";?>" />
        </div></td>
    </tr>
    <tr>
      <td><div align="right">Group:</div></td>
      <td><div align="left">
          <?php $size = sizeof($groups) - 1;
echo "<select name=\"group\" id=\"group\">";
for($count = $size;$count > 0;$count--)
{
echo "<option value=\"$count\"";
if($group == $count)
{
echo " selected";
}
echo ">$groups[$count]</option>";
} ?>
      </div><div align="left"></div></td>
      <td>Flags: <?php echo "$flags";?></td>
    </tr>
    <tr>
      <td><div align="right">Immunity Level:</div></td>
      <td colspan="2"><div align="left">
          <?php echo "$immune";?>
        </div></td>
    </tr>
<tr>
      <td><div align="right"> </div></td>
      <td colspan="2"><div align="left">
          <input name="admin_id" type="hidden" value="<?php echo "$id";?>" />
	  <input name="admin_id" type="hidden" value="<?php echo "$admin_id";?>" />
        </div></td>
    </tr>
    <tr>
      <td> </td>
      <td colspan="2"><input type="submit" name="button" id="button" value="Submit"></td>
    </tr>
  </table>
</form>

 

And my update page is this....

<?php
include 'config.php';
include 'access.php';

if(!isset($_SESSION['session_id']))
{
	die("You are not logged in and cannot view this page.");
}

$name = $_POST['name'];
$group = $_POST['group'];
$admin_id = $_POST['admin_id'];

$db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);

$query = "UPDATE $smadmin_table a INNER JOIN $smadmgrp_table b ON a.id = b.admin_id SET a.name = '" . mysql_real_escape_string($name) . "', b.group_id = '" . mysql_real_escape_string($group) . "' WHERE b.admin_id = '" . mysql_real_escape_string($admin_id) . "'";
mysql_query($query) or die("Query failed: " . mysql_error());

mysql_close($db);

echo '<br /><br /><br /><center><font size=\"+4\" color=\"red\">Admin updated.</center></font>';

print_r($_POST);
?>

[kickstart]

Hi

 

Mmm, can't see the issue. The SQL only appears to update the column group_id on the table that you say the name is being updated on.

 

All the best

 

Keith

[jay7981]

SET a.name = '" . mysql_real_escape_string($name) . "', b.group_id = '" . mysql_real_escape_string($group) . "'

shouldnt that update both the name colomn in sm_admins and the group_id in sm_admins_groups?

[kickstart]

Hi

 

Yes it should as far as I can see. But not name in $smadmgrp_table.

 

All the best

 

Keith

[jay7981]

ok then why isnt it working correctly, its renaming all the names in sm_admins to what ever is in the form ...

if i have this in sm_admins already ...

example:

id identity name

1 2929292 bob

2 3965930 tom

3 8563345 chris

4 7834564 pete

 

when the name of bob is changed to eric i get this in table

example:

id identity name

1 2929292 eric

2 3965930 eric

3 8563345 eric

4 7834564 eric

 

[kickstart]

Hi

 

I cannot see a reason. Can you echo out the SQL and try it directly on the database (to make sure the script isn't looping).

 

All the best

 

Keith

[jay7981]

ok i have put a band-aid on the issue by not allowing name changes LOL (they dont need to be changing names anyways...

 

just out of curiosity, i now i will be attempting to create a delete query today (after i wake up a bit more) i have heard and seen several opinions on record deletions via mysql, what approach would you suggest? i would need to delete the record from sm_admins that matches the $_post authid as sm_admins.identity, as well as the record that matches the sm_admins.id with sm_admins_groups.admin_id. What would the safest way to accomplish this as well as the most effective coding?

[jay7981]

ok here is what i have so far, im in the process of pulling a backup before i test it.... do you think this will do what i am wanting?

 

$query = "DELETE a, b, c, d FROM bioclan.sm_admin AS a INNER JOIN bioclan.sm_admins_group AS b INNER JOIN bioclan.admins AS c INNER JOIN bioclan.clan_members AS d WHERE a.id=b.admin_id and c.auth=d.authid";

[jay7981]

UPDATE*

 

ok that deleted EVERYTHING :`( so i shall wait for a reply to see where i went wrong

[kickstart]

Hi

 

I would not be at all surprised that your DELETE code deleted all the rows. You haven't specified in the where clause which rows to delete. Default will be to delete them all.

 

All the best

 

Keith

[jay7981]

ok then i guess i need some actual coding help, im not sure what i am missing here. would you mind showing me an example that would delete the needed row that matches the auth field in the "admins" table, as well as the authid field in the "clan_members" table, the id in the "sm_admins" table and finally the row that has the needed admin_id in the "sm_admins_groups" table.

 

i know what i want it to do ... just not sure how to obtain the result ...

[kickstart]

Hi

 

You would want something like this:-

 

$query = "DELETE a, b, c, d 
FROM bioclan.sm_admin AS a 
INNER JOIN bioclan.sm_admins_group AS b ON a.id=b.admin_id 
INNER JOIN bioclan.admins AS c ON b.admin_id = c.id 
INNER JOIN bioclan.clan_members AS d ON c.auth=d.authid
WHERE a.auth = 'somevalue'";

 

(note that I have guessed at the fields to join sm_admins_group with admins, you will need to check what columns join those 2 together).

 

Main thing is that you need to specify which auth to delete.

 

All the best

 

Keith

[jay7981]

ok i figured out the problem, and thank you for the help thus far.

 

the problem is this

(using your code as reference)

on tables a and b the a.id will match b.admin_id

on tables c and d the c.auth with match d.authid

on tables a c and d the a.identity will match c.auth and d.authid

 

there is nothing linking b.admin_id with anything other than a.id

 

i will be deleteing the row that matches $_POST('authid')

[kickstart]

Hi

 

Think that would translate to something like this

 

$query = "DELETE a, b, c, d 
FROM bioclan.sm_admin AS a 
INNER JOIN bioclan.sm_admins_group AS b ON a.id = b.admin_id
INNER JOIN bioclan.admins AS c ON b.admin_id = c.id AND a.identity = c.auth
INNER JOIN bioclan.clan_members AS d ON c.auth = d.authid AND a.identity = d.authid
WHERE a.identity = '".mysql_real_escape_string($_POST('authid'))."'"; 

 

All the best

 

Keith

[jay7981]

ok here is what i have done and it seems to be working,

$query = "DELETE a, b, c, d FROM bioclan.sm_admins AS a INNER JOIN bioclan.sm_admins_groups AS b ON a.id=b.admin_id INNER JOIN bioclan.admins AS c ON a.identity = c.auth INNER JOIN bioclan.clan_members AS d ON c.auth=d.authid WHERE c.auth = '" . $_POST['auth'] . "' AND a.id = '" . $_POST['id'] . "'";

 

i will try yours and see if that works as well, i trust your codeing skills better than my own.

[jay7981]

ok with your code i get

Query failed Unknown column 'c.id' in 'on clause'

 

here is the table structure for all the tables in question this may help us both....

Table 1 (holds all members of the clan weather they have admin or not)
clan_members	( don't want to delete from this table)
authid (same as identity in table 2 and auth in table 5)     
name     
rank

Table 2 (holds the info for all sourcemod admins)
sm_admins	(need to delete from this table)
id      
authtype     
identity (same as authid in table 1 and auth in table 5)     
password     
flags     
name     
immunity

Table 3 (Holds the sourcemod Groups that each person in table 2 can be assigned to)
sm_groups	(dont want to delete from this table)
id
flags     
name     
immunity_level

Table 4 (Holds the information of which admin is assigned to which group for sourcemod)
sm_admins_groups	(need to delete from this table)
admin_id (same as id in table 2)     
group_id (same as id in table 3)     
inherit_order

Table 5 (holds the info for all amxx admins)
admins		(need to delete from this table)
auth (same as authid in table 1 and identity in table 2)
name
password
access
flags

 

 

 

[kickstart]

Hi

 

Think that was a typo on my part

 

$query = "DELETE a, b, c, d 
FROM bioclan.sm_admin AS a 
INNER JOIN bioclan.sm_admins_group AS b ON a.id = b.admin_id
INNER JOIN bioclan.admins AS c ON a.identity = c.auth
INNER JOIN bioclan.clan_members AS d ON c.auth = d.authid AND a.identity = d.authid
WHERE a.identity = '".mysql_real_escape_string($_POST('authid'))."'"; 

 

Beyond that I would need to properly understand how the tables hang together.

 

All the best

 

Keith