title: Show menu items to users in MySQL database on a per user permission basis with PHP?
body: <p>I have a MySQL database table for <code>users</code> and a new one for <code>menu links</code>.  Using PHP, more specifically I am also using Laravel.</p>
  
  <p>I need to build a User Menu that displays menu items on a per user permission basis.</p>
  
  <p>Typically this is done using <code>user groups</code> however my requirement is to build it on a per-user-basis!</p>
  
  <p>Meaning every menu item needs to have a <code>yes</code> or <code>no</code> value saved somewhere for every single user in the database.</p>
  
  <p>I then need to display this menu to each user, showing only the menu items they are allowed to view.</p>
  
  <p>I have the user and links MySQL database schema below.</p>
  
  <p><strong>What I need help with is, I believe I need to add another 3rd table <code>user_link_permissions</code> that will store the setting for each user and menu item to determine if the user can view the menu item or not.</strong> </p>
  
  <p><strong>I am not sure how to build the PHP to show only the menu items a user is allowed to view and could also use some help in how that 3rd table might need to look like please?</strong></p>
  
  <p>I deally in the PHP code that will be building the menu HTML output, I think it would be nice to possibbly have a method that checks each menu item record in a loop to see if the current user has permission to view it or not.... example</p>
  
  <pre><code>// Array of menu items from MySQL Database or even just a MySQL result?
  $menuItems = array();
  
  foreach ($menuItems as $key =&gt; $value) {
  
      // can cureent user view this menu item record or not?
      if($this-&gt;user-&gt;canViewMenuItem($value)){
          // show menu item
      }
  
  }
  </code></pre>
  
  <hr>
  
  <p><strong>Users Table</strong>  </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `users` (
    `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
    `password` varchar(160) COLLATE utf8_unicode_ci NOT NULL,
    `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `first_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
    `last_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
    `phone` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `address_street` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `address_city` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `address_state` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `address_postal_code` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `address_country` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'USA',
    `job_position` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `user_role` enum('admin','manager','employee') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'employee',
    `payday_group` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `default_user_photo_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `last_user_photo_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `created_date` datetime NOT NULL,
    `last_login_date` datetime DEFAULT NULL,
    `updated_date` datetime DEFAULT NULL,
    `login_counter` bigint(20) NOT NULL DEFAULT '0',
    `total_time_worked` bigint(20) DEFAULT NULL,
    `user_notes` text COLLATE utf8_unicode_ci,
    `time_zone` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'US/Central',
    `clocked_in` tinyint(1) NOT NULL DEFAULT '0',
    `status` tinyint(1) NOT NULL DEFAULT '1',
    `webcam` tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `users_username_unique` (`username`),
    UNIQUE KEY `users_email_unique` (`email`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=64 ;
  </code></pre>
  
  <p><strong>Links table</strong>  </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `intranet_links` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) DEFAULT NULL,
    `description` text NOT NULL,
    `url` varchar(255) DEFAULT NULL,
    `permission` varchar(50) NOT NULL DEFAULT 'admin',
    `notes` text,
    `active` int(2) NOT NULL DEFAULT '1',
    `sort_order` int(11) DEFAULT NULL,
    `parent_id` int(10) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`),
    UNIQUE KEY `id` (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
  </code></pre>
  
  <p>To slightly complicate things a little more, my menu will also have a hierachy like categories/folders/directories.  So that 1 menu item can have child menu items o my actual menu output would like like the image below, except that each menu items will also be shown or not shown on a per user permission setting basis!</p>
  
  <p><img src="https://i.stack.imgur.com/3R4ZL.png" alt="enter image description here"></p>
  
  <p>For now my question is just about how to structure the 3rd DB table and then how to query and show the correct links to each user.  After that part is complete though I will then also have a settings page in which each menu item can be set to <code>yes/no</code> for each user in the database.  Similar to this SugarCRM permission page... with the exception that the top horizontal columns will be <code>links</code> and the vertical records will be users....</p>
  
  <p><img src="https://i.stack.imgur.com/6DYAT.png" alt="enter image description here"></p>