Jump to content

Create HTML table from Category and Subcategory


thara

Recommended Posts

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.

table-layout.jpg.ea8723ee8617753fbb25f7311cae6900.jpg

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

Thank you.

 

Edited by thara
Link to comment
Share on other sites

Your array will look like this

image.thumb.png.2dc3059cf6178197975de4225da27450.png

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

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?

 

 

Link to comment
Share on other sites

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>

image.png.ca7fd45092959fba805e6c5f624d6268.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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:

Untitled-2.jpg.08e11fdee20ff410838670176554eda7.jpg

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?

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
×
  • 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.