Create HTML table from Category and Subcategory

[thara]

I do have a sql table named "module" and a parent module may have a sub module. Each parent module can have a maximum of 9 sub modules of three per <td>.

This is how this "module" table looks like:

+-----------+------------------+----------+------+--------+---------------------+
| module_id | name             | page_url | icon | parent | created_date        |
+-----------+------------------+----------+------+--------+---------------------+
|         1 | User Modules     |          | NULL |   NULL | 2021-07-21 11:46:16 |
|         2 | Items            |          | NULL |      1 | 2021-07-21 11:46:16 |
|         3 | Add New Item     |          | NULL |      2 | 2021-07-21 11:46:16 |
|         4 | View Item        |          | NULL |      2 | 2021-07-21 11:46:16 |
|         5 | Category         |          | NULL |      2 | 2021-07-21 11:46:16 |
|         6 | Brand            |          | NULL |      2 | 2021-07-21 11:46:16 |
|         7 | Unit             |          | NULL |      2 | 2021-07-21 11:46:16 |
|         8 | Purchase         |          | NULL |      1 | 2021-07-21 11:46:16 |
|         9 | Add New Purchase |          | NULL |      8 | 2021-07-21 11:46:16 |
|        10 | view Purchase    |          | NULL |      8 | 2021-07-21 11:46:16 |
|        11 | Due Invoice      |          | NULL |      8 | 2021-07-21 11:46:16 |
|        12 | Return           |          | NULL |      8 | 2021-07-21 11:46:16 |
|        13 | Purchase Log     |          | NULL |      8 | 2021-07-21 11:46:16 |
|        14 | Inventory        |          | NULL |      1 | 2021-07-21 11:46:16 |
|        15 | Stock Transfer   |          | NULL |     14 | 2021-07-21 11:46:16 |
|        16 | Stock Adjustment |          | NULL |     14 | 2021-07-21 11:46:16 |
|        17 | Batch Control    |          | NULL |     14 | 2021-07-21 11:46:16 |
|        18 | Stock Take       |          | NULL |     14 | 2021-07-21 11:46:16 |
+-----------+------------------+----------+------+--------+---------------------+

My desired HTML table layout looks somthing similar to this attach image.

I would like to know how we can do it in php, if is possible.

Thank you.

 

Edited July 23, 2021 by thara

[Barand]

 

[Barand]

Looking closer at your data, the function would not need to be recursive. A simple nested loop would do once you have the array as you don't have an indeterminate number of levels (just 2)

[thara]

Sir, I have checked that your above linked post, before asking this. But I couldn't figure it out.

[Barand]

Your array will look like this

Each id in the array items points to the index of that items child array

[edit]...

To get you started

foreach ($data[1] as $main) {
    echo $main['name'] . '<br>';
    foreach ($data[$main['id']] as $mod) {
          echo "- {$mod['name']}<br>";
    }
}

 

Edited July 23, 2021 by Barand

[thara]

Thank you Sir.

Your demostration was really helped me to get it started.

This is how I tried it so far:

$sql ="SELECT module_id
            , name
            , IFNULL(parent, 0)
       FROM module
       ORDER BY module_id";                   
$stmt = $mysqli->prepare($sql);
if ($stmt) {
  $stmt->execute();    
  $stmt->store_result();  
  $numrows = $stmt->num_rows;
 
  if ($numrows >= 1) {  
    $stmt->bind_result($modId,$name,$parent);  
    while ($stmt->fetch()) {    
      $modList[$parent][] =['id' => $modId, 'name' => $name];    
    }
    $stmt->close();
    unset($stmt);   
  }   
}

$tbl = '<table class="table table-bordered">';
foreach ($catList[1] as $main) {
  $tbl .= "<tr><td>{$main['name']}</td><td>";
  //echo $main['name'] . '<br>';
  foreach ($catList[$main['id']] as $mod) {
    //$modItem = array_chunk($mod, 3);

    $tbl .= "{$mod['name']}";
    //echo "- {$mod['name']}<br>";
  }
  $tbl .= "</td></tr>";
}

$tbl .= "</table>";

echo $tbl;

 

This is ok, But there is a problem. Sir may I know how I get 3 sub modules per each <td> in the table?

 

 

[Barand]

I'd use array_chunk, as you were planning to do.

[thara]

Yes Sir I tried with array_chunk, but I couldn't get it to work as l expected. 

[Barand]

For the benefit of those of us who can't read your mind, what were you expecting? In what way did it not work?

[thara]

Sir, I need single <tr> with 4 <td> for one module. (for parent and child). Chuck of 3 childs need to be get into one td. That is the issue I have. 

[Barand]

Having chunked your array of children, loop through the chunks putting the submodules from each chunk into a <td>..</td>.

(You might want to pad the child array to 9 first to ensure you always get 3 columns of 3. Tables like their rows to have the same number of cells)

[Barand]

No peeping until you have tried again

<?php
$sql = "SELECT module_id, name, parent
        FROM module
        ORDER BY module_id";
$res = $pdo->query($sql);
//
// store arrays of items for each parent in an array
//
while (list($id, $name, $parent) = $res->fetch(PDO::FETCH_NUM)) {
    $data[$parent][] = array('id'=>$id, 'name'=>$name);
}

//echo '<pre>' . print_r($data, 1) . '</pre>';

$modules = '';
foreach ($data[1] as $main) {
    $modules .=  "<tr class='w3-hover-sand'>
                    <td style='background: #f8f8ff;'><input type='checkbox' name='pbox[$main[id]]' value='1'></td>
                    <td>$main[name]</td>
                 ";
    $children = array_pad($data[$main['id']], 9, ['id'=>'', 'name'=>'']);     // ensure always 9 chilren (3 columns)

    $chunks = array_chunk($children, 3);
    foreach ($chunks as $mods) {
        $modules .=   "<td class='submods'>";
            foreach ($mods as $m) {
                $modules .= $m['id'] ? "<input type='checkbox' name='cbox[$m[id]]' value='1'>&ensp;$m[name]<br>" : '<br>';
            }
        $modules .=   "</td>";
    }
    $modules .= "</tr>\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 19515, 64bit)">
<title>Modules</title>
<meta name="author" content="Barry Andrew">
<meta name="creation-date" content="07/23/2021">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
    .submods {
        width: 300px;
        white-space: nowrap;
    }
</style>
</head>
<body>
<div class='w3-container w3-margin w3-responsive'>
    <table class='w3-table w3-bordered'>
        <tr class='w3-light-gray'>
            <th><input type='checkbox' name='cbox' value='1'></th>
            <th>Parent module</th>
            <th colspan='3'>Submodules</th>
        </tr>
        <?=$modules?>
    </table>
</div>
</body>
</html>

[thara]

Thank you Sir,

I have tried, but my array_chunk was in wrong loop. (I have tried it inside second foreach)

I have another question to clarify with you. 

When I trying to edit user selected checkboxes, I am currently using 2 queries like this:
 

  // Get All Modules:
  $sql ="SELECT module_id, name, IFNULL(parent, 0) FROM module ORDER BY module_id";                   
  $res = $pdo->query($sql);

  while (list($id, $name, $parent) = $res->fetch(PDO::FETCH_NUM)) {
    $data[$parent][] = array('id'=>$id, 'name'=>$name);
  }

  // Gell User Selected Modules:
  $sql ="SELECT module_id FROM user_permission WHERE user_id = ? ORDER BY module_id";                   
  $stmt = $pdo->prepare($sql);
  $stmt->execute([$pageID]);

  while (list($module_id) = $stmt->fetch(PDO::FETCH_NUM)) {
    $mid[] = $module_id;
  }

To display selected checkbox in edit form, I am doing it like this inside 1st and 3rd foreach.

$checked = (in_array($main['id'], $mid)) ? " checked='checked'" : '';

$checked = (in_array($m['id'], $mid)) ? " checked='checked'" : '';

Sir, may I know, is there a way to do this with single query? Left Join didn't work for me.

[Barand]

Here's the single query. If the user has permission to use a module, the permission column = 1 otherwise 0.

If the user has permission to use a module's parent module then the user can use all the children of the parent.

SELECT m.module_id
     , name
     , IFNULL(parent, 0)
     , CASE WHEN p2.module_id IS NULL
            THEN CASE WHEN p.module_id IS NULL
                      THEN 0
                      ELSE 1
                      END
            ELSE 1
            END as permission
FROM module m
     LEFT JOIN user_permission p ON m.module_id = p.module_id
                                 AND p.user_id = 2
     LEFT JOIN user_permission p2 ON m.parent = p2.module_id
                                  AND p2.user_id = 2
ORDER BY m.module_id ;

 

Edited July 25, 2021 by Barand

[thara]

Thank you sir,

One thing, Can we set all permission to 0 including parent, if a user select only parent module. In this case it is not needed parent permission, if user select only parent.

[Barand]

If user selects only parent module and you set parent permission to 0 (ie remove permission record) how do you then know user has set parent permission?

[thara]

Sir, I have showing a message to user that saying don't select only parent in my front end. But if they select only parent deliberately or by mistake, I need to ignore it from backend. Thats why I am looking for such a solution.

[Barand]

Then validate the input in the php so only valid data gets to the database.

PS...

I would have it so that selecting the parent checkbox resulted in all the submodules of that parent being selected (checked) also. If they then uncheck a submodule, unset the parent (as they no longer have all)

Edited July 25, 2021 by Barand

[thara]

19 hours ago, Barand said:

Then validate the input in the php so only valid data gets to the database.

Sir, I tried it something like this:

My parent and child module arrays changed as below:
 

[pmod] => Array (
	[2] => 1
	[8] => 1
	[14] => 1
)

[cmod] => Array (
	[8] => Array (
        	[9] => 1
        	[10] => 1
        	[11] => 1
		  )
	[14] => Array (
       		[15] => 1
     		[16] => 1 
		  )
)

 

This is my PHP:

// Insert into "user_permission" table:
if(!empty($_POST['pmod']) && !empty($_POST['cmod'])) {
  $modCheked=[];

  foreach ($_POST['cmod'] as $pid => $child) {
    foreach ($child as $cid => $value) {
        array_push($modCheked, $pid, $cid);
        $modules = array_unique($modCheked);
    }
  }

  $sql = "INSERT INTO user_permission (module_id,user_id) VALUES (?,?)";
  $stmt = $pdo->prepare($sql);
  foreach ($modules as $k => $mid) {
      $module_id  =  intval($mid);  
      $stmt->execute([$module_id, $lastInsertID]);
  }
}

From this code, it is inserted all IDs from `cmod` array into DB.

[thara]

Sir, I am having a problem with your above single query solution. As you have mentioned, it become permission column to 1, for selected parents and its all child modules.

Look at this Example:

Query Result:

SELECT m.module_id
     , name
     , IFNULL(parent, 0)
     , CASE WHEN p2.module_id IS NULL
            THEN CASE WHEN p.module_id IS NULL
                      THEN 0
                      ELSE 1
                      END
            ELSE 1
            END as permission
FROM module m
     LEFT JOIN user_permission p ON m.module_id = p.module_id
                                 AND p.user_id = 38
     LEFT JOIN user_permission p2 ON m.parent = p2.module_id
                                  AND p2.user_id = 38
ORDER BY m.module_id ;

+-----------+------------------+-------------------+------------+
| module_id | name             | IFNULL(parent, 0) | permission |
+-----------+------------------+-------------------+------------+
|         1 | User Modules     |                 0 |          0 |
|         2 | Items            |                 1 |          0 |
|         3 | Add New Item     |                 2 |          0 |
|         4 | View Item        |                 2 |          0 |
|         5 | Category         |                 2 |          0 |
|         6 | Brand            |                 2 |          0 |
|         7 | Unit             |                 2 |          0 |
|         8 | Purchase         |                 1 |          1 |
|         9 | Add New Purchase |                 8 |          1 |
|        10 | view Purchase    |                 8 |          1 |
|        11 | Due Invoice      |                 8 |          1 |
|        12 | Return           |                 8 |          1 |
|        13 | Purchase Log     |                 8 |          1 |
|        14 | Inventory        |                 1 |          0 |
|        15 | Stock Transfer   |                14 |          0 |
|        16 | Stock Adjustment |                14 |          0 |
|        17 | Batch Control    |                14 |          0 |
|        18 | Stock Take       |                14 |          0 |
+-----------+------------------+-------------------+------------+

Sir, How can we make permision column to 1 only for selected modules?

 

 

[Barand]

22 hours ago, Barand said:

I would have it so that selecting the parent checkbox resulted in all the submodules of that parent being selected (checked) also. If they then uncheck a submodule, unset the parent (as they no longer have all)

To me, checking the parent box indicates that all the submodules in that group are selected. But I can only suggest and give examples of how. You are completely at liberty to change things to how you want it to work, such as not storing permissons for parent modules. As I have stated before, I am not telepathic.

[thara]

Sir, I do not see any error in the query pointed out by you.

Sir, Is it possible to change that single query by setting the permissions to 1 only for the parent and child modules selected by the user?

[Barand]

SELECT m.module_id
     , name
     , IFNULL(parent, 0)
     , CASE WHEN p.module_id IS NULL
            THEN 0
            ELSE 1
            END as permission
FROM module m
     LEFT JOIN user_permission p ON m.module_id = p.module_id
                                 AND user_id = 1
ORDER BY m.module_id ;