Access Level with user and page level

michelle1404 · August 11, 2021

I have a small php script for crm. i have designed it by module wise. Admin can grant access to each module and sub-items under that modules. 1st step: admin will select modules(Sales, Purchase, Finance) for which he has to give access to a particular user and save the module ids in module_access table. 2nd step : only selected module's sub-items will display to admin , in which he can select to give access

1st step is done. but in 2nd step i am not able to display only selected modules with the sub-items.

Here is the table where i stored my data.

module table

mid | mod_name | parent_id  | link
1   | Sales    | 0          | #
2   | Purchase | 0          | #
3   | Finance  | 0          | #
4   | Clients  | 1          | clients.php
5   | Quotes   | 1          | quotes.php
6   | Vendors  | 2          | vendors.php
7   | POs      | 2          | pos.php
8   | Invoices | 3          | invoices.php
9   | Taxes    | 3          | taxes.php
10  | Expense  | 3          | expenses.php

module_access table

id| page_id | user 
1 | 1       | 1
2 | 3       | 1
3 | 4       | 1
4 | 9       | 1
5 | 10      | 1

user is from another table users where i store user info

In this example i want to display like this

Sales
   Clients (with a checkbox)
Finance
   Taxes   (with a checkbox)
   Expense (with a checkbox)

I tried like this

<?php
          $items = array();

$res = $con->prepare("SELECT m.mid, m.mod_name, m.parent_id, ma.id, ma.page_id, ma.user FROM modules m INNER JOIN module_access ma ON m.mid=ma.page_id WHERE ma.user=? ORDER BY m.mid");
$res->execute([$uID]);
foreach($res as $row) {
 $items[$row['parent_id']][] = $row;
}
function showMenu($items, $parent = null) {

 $index = $parent == null ? '0' : $parent;

 if (empty($items[$index])) {
 
  return;
 }

 echo '<ul', $parent == null ? ' id="subitems" ... ' : '', '>';

 foreach ($items[$index] as $child) {
  echo '<li rel="', $child['mid'], '">', htmlentities($child['mod_name']);
  showMenu($items, $child['mid']);
  echo '</li>';
 }

 echo '</ul>';
}

showMenu($items);
?>

But it displays like this

Sales
Finance

I am not getting how to query this from database.

Barand · August 11, 2021

There's an example in this forum here

michelle1404 · August 11, 2021

@BarandSorry for my mistake, I couldn't edit above post. in my module_access table, i have only module ids like this

id| page_id | user 
1 | 1       | 1
2 | 3       | 1

So, when i join , it shows only Sales and Finance as below. I want to display the sub items also.

Sales
Finance

Edited August 11, 2021 by michelle1404

Barand · August 11, 2021

Something like this

<?php
require 'db_inc.php'

$uid = 1;

$stmt = $con->prepare("SELECT  mid
                             , mod_name
                             , link
                             , parent_id
                        FROM module m 
                             JOIN module_access ma ON m.mid = ma.page_id
                        WHERE ma.user = ?;
                        ");
$stmt->execute( [$uid] );

$mods = [];
foreach ($stmt as $row) {
    $mods[$row['parent_id']][] = $row;
}

function showMenu(&$arr, $parent = 0, $level = 0) 
{
    if (!isset($arr[$parent])) return;
    echo "<ul>\n";
    foreach($arr[$parent] as $rec) {
        echo "<li class='li$level'><a href='{$rec['link']}'> {$rec['mod_name']} </a>\n";
        if (isset($arr[$rec['mid']]))
            showMenu($arr, $rec['mid'], $level+1);
        echo "</li>\n";
    }
    echo "</ul>\n";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 19.5 (Build 19523, 64bit)">
<meta name="author" content="Barand">
<meta name="creation-date" content="08/11/2021">
<title>Example</title>
<style type='text/css'>
    li {
        padding: 8px;
    }
    .li0 {
        margin-left: 8px;
    }
    .li1 {
        margin-left: 28px;
    }
    .li2 {
        margin-left: 48px;
    }
</style>
</head>
<body>
    <?= showMenu($mods, 0) ?>
</body>
</html>

michelle1404 · August 12, 2021

@Barand I tried the same

<?php
		  //$items = array();

$res = $con->prepare("SELECT mid, mod_name, parent_id, link FROM modules m JOIN module_access ma ON m.mid=ma.page_id WHERE ma.user=? ORDER BY m.mid");
$res->execute([$uID]);
$mods = [];
foreach ($res as $row) {
    $mods[$row['parent_id']][] = $row;
}

function showMenu(&$arr, $parent = 0, $level = 0) 
{
    if (!isset($arr[$parent])) return;
    echo "<ul>\n";
    foreach($arr[$parent] as $rec) {
        echo "<li class='li$level'><a href='{$rec['link']}'> {$rec['mod_name']} </a>\n";
        if (isset($arr[$rec['mid']]))
            showMenu($arr, $rec['mid'], $level+1);
        echo "</li>\n";
    }
    echo "</ul>\n";
}
 showMenu($mods, 0);

?>

But it shows only module names, not the sub-items under that.

Barand · August 12, 2021

Wierd!. Whether I run my code or your above code (modified only to use table name "module" to match my DB) I get exactly the same output ...

image.png.23a90888f8da538aa5498a6e2ccfd635.png

The generated HTML being ...

<ul>
<li class="li0"><a href="#"> Sales </a>
<ul>
<li class="li1"><a href="clients.php"> Clients </a>
</li>
</ul>
</li>
<li class="li0"><a href="#"> Finance </a>
<ul>
<li class="li1"><a href="taxes.php"> Taxes </a>
</li>
<li class="li1"><a href="expenses.php"> Expense </a>
</li>
</ul>
</li>
</ul>

What does your query return?

mysql> SELECT  mid
    ->       , mod_name
    ->       , link
    ->       , parent_id
    ->  FROM module m
    ->       JOIN module_access ma ON m.mid = ma.page_id
    ->  WHERE ma.user = 1;
+-----+----------+--------------+-----------+
| mid | mod_name | link         | parent_id |
+-----+----------+--------------+-----------+
|   1 | Sales    | #            |         0 |
|   3 | Finance  | #            |         0 |
|   4 | Clients  | clients.php  |         1 |
|   9 | Taxes    | taxes.php    |         3 |
|  10 | Expense  | expenses.php |         3 |
+-----+----------+--------------+-----------+

michelle1404 · August 12, 2021

My Query return

query.PNG.db482e53a7c3bbee080824dacfc0bbb6.PNG

This is how it displays

<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 19.5 (Build 19523, 64bit)">
<meta name="author" content="Barand">
<meta name="creation-date" content="08/11/2021">
<title>Example</title>
<style type='text/css'>
    li {
        padding: 8px;
    }
    .li0 {
        margin-left: 8px;
    }
    .li1 {
        margin-left: 28px;
    }
    .li2 {
        margin-left: 48px;
    }
</style>
</head>
<body>
    <ul>
<li class='li0'><a href='#'> Sales </a>
</li>
<li class='li0'><a href='#'> Finance </a>
</li>
</ul>
</body>
</html>

As it joins with the mid page_id from modules and module_access respectively, it wont display those ids which is not there in module_access table . So it is not getting displayed, as module_access table has only 1,3 ids which matches with mid from modules table.

Edited August 12, 2021 by michelle1404

michelle1404 · August 12, 2021

Yes, I got the answer.

I modified query like this

SELECT mid, mod_name, parent_id, link FROM modules m JOIN module_access ma ON (m.mid=ma.page_id OR ma.page_id=m.parent_id) WHERE ma.user=1 ORDER BY m.mid

Thanks a lot @Barandfor your valuable time and patience.

Barand · August 12, 2021

The data you posted:

mysql> select * from module;
+-----+----------+-----------+--------------+
| mid | mod_name | parent_id | link         |
+-----+----------+-----------+--------------+
|   1 | Sales    |         0 | #            |
|   2 | Purchase |         0 | #            |
|   3 | Finance  |         0 | #            |
|   4 | Clients  |         1 | clients.php  |
|   5 | Quotes   |         1 | quotes.php   |
|   6 | Vendors  |         2 | vendors.php  |
|   7 | POs      |         2 | pos.php      |
|   8 | Invoices |         3 | invoices.php |
|   9 | Taxes    |         3 | taxes.php    |
|  10 | Expense  |         3 | expenses.php |
+-----+----------+-----------+--------------+

mysql> select * from module_access;
+----+---------+------+
| id | page_id | user |
+----+---------+------+
|  1 |       1 |    1 |
|  2 |       3 |    1 |
|  3 |       4 |    1 |
|  4 |       9 |    1 |
|  5 |      10 |    1 |
+----+---------+------+

Your revised query gives me

mysql> SELECT mid
    ->     , mod_name
    ->     , parent_id
    ->     , link
    -> FROM module m
    ->     JOIN module_access ma ON (m.mid=ma.page_id OR ma.page_id=m.parent_id)
    -> WHERE ma.user=1
    -> ORDER BY m.mid;
+-----+----------+-----------+--------------+
| mid | mod_name | parent_id | link         |
+-----+----------+-----------+--------------+
|   1 | Sales    |         0 | #            |
|   3 | Finance  |         0 | #            |
|   4 | Clients  |         1 | clients.php  |
|   4 | Clients  |         1 | clients.php  |
|   5 | Quotes   |         1 | quotes.php   |
|   8 | Invoices |         3 | invoices.php |
|   9 | Taxes    |         3 | taxes.php    |
|   9 | Taxes    |         3 | taxes.php    |
|  10 | Expense  |         3 | expenses.php |
|  10 | Expense  |         3 | expenses.php |
+-----+----------+-----------+--------------+

and the script now outputs...

image.png.a3d05dec6a2292c7e4d4535f6b161eb0.png

I can only assume that your data is not what you posted.

[edit]

Perhaps you have a varchar instead of int that contains hidden whitespace so they are not matching in the joins?

What do these two queies output?...

SELECT id
     , page_id
     , HEX(page_id)
     , user
     , HEX(user)
FROM module_access;

SELECT mid
     , HEX(mid)
     , parent_id
     , HEX(parent_id)
FROM module;

Edited August 12, 2021 by Barand

Barand · August 12, 2021

Forget the above. I have now realized that I completely missed that your module_access on had links to the 2 parent modules.

Here's an alternative approach

$uID = 1;

$res = $con->prepare("SELECT  m1.mod_name as name1
                            , m2.mod_name as name2
                            , m2.link as link2
                        FROM module m1
                            JOIN module m2 ON m1.mid = m2.parent_id  
                            JOIN module_access ma ON (m1.mid = ma.page_id) 
                        WHERE ma.user=? 
                        ORDER BY m1.mid
                        ");
$res->execute([$uID]);
$mods = [];
foreach ($res as $row) {
    if (!isset($mods[$row['name1']])) {
        $mods[$row['name1']] = [];
    }
    $mods[$row['name1']][$row['name2']] = $row['link2'] ;
}

The mods array now contains...

Array
(
    [Sales] => Array
        (
            [Quotes] => quotes.php
            [Clients] => clients.php
        )

    [Finance] => Array
        (
            [Invoices] => invoices.php
            [Expense] => expenses.php
            [Taxes] => taxes.php
        )

)

... which you can loop through to give the output

function showMenu(&$arr) 
{
    echo "<ul>\n";
    foreach($arr as $parent => $modules) {
        echo "<li class='li0'>$parent\n";
        echo "<ul>\n";
        foreach ($modules as $name => $link) {
            echo "<li class='li1'><a href='$link'>$name</a>\n";
        }
        echo "</ul>\n";
        echo "</li>\n";
    }
    echo "</ul>\n";
}

giving

image.png.46a77f5c45618fbd62147ccd3e139293.png

Sign In

Access Level with user and page level

Recommended Posts

michelle1404

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

michelle1404

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

michelle1404

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

michelle1404

Link to comment

Share on other sites

michelle1404

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information