How To Reference Other Tables

Question

So what I am trying to do is so that different groups have different permissions that will allow them to look at different things. I have set it up so the table groups has multiple columns, the first 2 being groupid and groupname, then the rest are the permissions, either 1 as enabled or 0 as disabled.

<?php

ob_start();
session_start();
require_once 'dbconnect.php';

$res=mysql_query("SELECT * FROM users WHERE userId=".$_SESSION['user']);
$userRow=mysql_fetch_array($res);

$sql=mysql_query("SELECT * FROM groups WHERE groupid=".$userRow['usergroup']);
$groupRow=mysql_fetch_array($sql);

?>

<div id="sidebar-menu" class="main_menu_side hidden-print main_menu">
  <div class="menu_section">
    <h3>General</h3>
    <ul class="nav side-menu">
      <li><a><i class="fa fa-home"></i> Home <span class="fa fa-chevron-down"></span></a>
        <ul class="nav child_menu">
          <li><a href="home.php">Dashboard</a></li>
          <?php if ($groupRow['Test'] == "1") { echo '<li><a href="index2.html">Dashboard2</a></li>'; } ?>
            <li><a href="index3.html">Dashboard3</a></li>
        </ul>
      </li>
    </ul>
  </div>
  <div class="menu_section">
    <h3>Live On</h3>
    <ul class="nav side-menu">
      <li><a><i class="fa fa-bug"></i> Additional Pages <span class="fa fa-chevron-down"></span></a>
        <ul class="nav child_menu">
          <li><a href="e_commerce.html">E-commerce</a></li>
          <li><a href="projects.html">Projects</a></li>
          <li><a href="project_detail.html">Project Detail</a></li>
          <li><a href="contacts.html">Contacts</a></li>
          <li><a href="profile.html">Profile</a></li>
        </ul>
      </li>
    </ul>
  </div>
</div>

Any suggestions would be great!

Thanks,
cNTr1nity

P.S DBConnect is just a PHP file that connects the other PHP files to the MySQL database.

While this won't fix your problem, please note the `mysql_` constructor is [**deprecated as of PHP 5.5**](https://wiki.php.net/rfc/mysql_deprecation), and is [**removed in PHP 7**](https://wiki.php.net/rfc/remove_deprecated_functionality_in_php7#extmysql). Please consider switching to either [**MySQLi**](http://php.net/manual/en/book.mysqli.php) or [**PDO**](http://php.net/manual/en/book.pdo.php), ensuring that you also use [**prepared statements**](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) to prevent [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) :) — Obsidian Age, Aug 07 '17 at 21:32

Lew Perren · Accepted Answer · 2017-08-07T23:01:38.713

Your test echo is on the right lines. Indeed you seem to be virtually complete. I would use a single select query with a join between the users and groups tables with a foreign key of usergroup in the user table as the link and the session user as the WHERE criteria, so:

 $res=mysql_query("SELECT groups.*, users.* FROM groups 
INNER JOIN users ON groups.groupid = users.usergroup WHERE
 users.userId =" .$_SESSION['user']);

Then you can use multiple if conditions to echo the correct additional href lines as required.

IMPORTANT: I would also suggest taking onboard @Obsidian Age comments by updating to mysqli/pdo and protecting your code from the risks of sql injection. The docs on the later mysqli are here and helpful SO post on protecting against sql injection. So a good starting place to protect your code would be to use mysqli prepared parameter statements something like (assuming users.userId is an integer):

$s = $mysqliConnection->prepare('SELECT groups.*, users.* FROM groups 
    INNER JOIN users ON groups.groupid = users.usergroup WHERE
     users.userId = ?');
$s->bind_param('i',$_SESSION['user']);

$s->execute();

See docs on mysqli prepared parameter statements here for more advice and also read the SO reference above for other security measures.

How To Reference Other Tables

1 Answers1