Jump to content

Access Level with user and page level


michelle1404
 Share

Go to solution Solved by michelle1404,

Recommended Posts

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.

Link to comment
Share on other sites

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>

 

  • Thanks 1
Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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 |
+-----+----------+--------------+-----------+

 

  • Thanks 1
Link to comment
Share on other sites

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 by michelle1404
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.