xmatt1
27 Jul 2007, 12:14 PM
Hello everyone!
Well, i was looking for a great example about tree menu view with PHP and MySQL and i didnt found a GREAT EXAMPLE so i did it in my self and im here for show u as it is easy :D
so lets start it. This Example its to build a tree menu that check the users permissions and our profiles so the our database look like this
CREATE TABLE profile (
idProfile INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY(idProfile)
)
TYPE=InnoDB;
CREATE TABLE page (
idPage INT(11) NOT NULL AUTO_INCREMENT,
parent_id INT(11) NULL,
name VARCHAR(50) NULL,
url VARCHAR(80) NULL,
action VARCHAR(20) NULL,
visible TINYINT(3) UNSIGNED NULL,
order INT(11) NULL,
PRIMARY KEY(idPage)
)
TYPE=InnoDB;
CREATE TABLE user (
idUser INT(11) NOT NULL AUTO_INCREMENT,
idProfile INT(11) NOT NULL,
name VARCHAR(50) NOT NULL,
login VARCHAR(12) NOT NULL,
password VARCHAR(12) NOT NULL,
accessDate DATETIME NULL,
lastAccess DATETIME NULL,
visits INT(11) NULL,
PRIMARY KEY(idUser),
INDEX user_FKIndex1(idProfile),
FOREIGN KEY(idProfile)
REFERENCES profile(idProfile)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB;
CREATE TABLE permission (
idPage INT(11) NOT NULL,
idProfile INT(11) NOT NULL,
INDEX permission_FKIndex2(idProfile),
INDEX permission_FKIndex1(idPage),
FOREIGN KEY(idProfile)
REFERENCES profile(idProfile)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(idPage)
REFERENCES page(idPage)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB;
Great so u have our databese structure and now you have to insert some records.
i'll explain our database structure so
1. We have a profile table that we insert the users profile if is Admin or its only a simple user
2. We have a page table that we insert the idProfile that check what profile can access this page and etc..
3. We have a user table that we insert a user with ur profile in profile table.
4. and then we have a permission table that define what profile acces page if the user can acces or not! cool ;)
so now lets to the visual part. First create a html file like this:
index.html:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>EXT - Tree View with PHP and MySQL </title>
<link rel="stylesheet" type="text/css" href="../includes/ext/resources/css/ext-all.css" />
<script type="text/javascript" src="../includes/ext/adapter/ext/ext-base.js"></script> <!-- ENDLIBS -->
<script type="text/javascript" src="../includes/ext/ext-all.js"></script>
<script type="text/javascript" src="menu.js"></script>
<style type="text/css">
#tree {
float:left;
margin:20px;
border:1px solid #c3daf9;
width:250px;
height:300px;
overflow:auto;
}
.folder .x-tree-node-icon{
background:transparent url(../includes/ext/resources/images/default/tree/folder.gif);
}
.x-tree-node-expanded .x-tree-node-icon{
background:transparent url(../includes/ext/resources/images/default/tree/folder-open.gif);
}
</style>
</head>
<body>
<div id="tree"></div>
</body>
</html>
and now the 2nd step javascript code:
menu.js:
var TreeTest = function(){
// shorthand
var Tree = Ext.tree;
return {
init : function(){
// yui-ext tree
var tree = new Tree.TreePanel('tree', {
animate:true,
loader: new Tree.TreeLoader({dataUrl:'get-nodes.php'}),
containerScroll: true,
});
// add a tree sorter in folder mode
// set the root node
var root = new Tree.AsyncTreeNode({
text: 'Menu',
draggable:false, // disable root node dragging
id:'source'
});
tree.setRootNode(root);
tree.render();
root.expand(false,false);
}
};
}();
Ext.EventManager.onDocumentReady(TreeTest.init, TreeTest, true);
and now our user class php - ps: i have a extend DB class in my configure.inc.php but its easy for u understand that :D
users.inc.php:
class users {
var $db;
function users()
{
$this->db = $GLOBALS['db'];
}
function getParent()
{
$sql = "SELECT * FROM page WHERE parent_id = 0 ";
$query = $this->db->query($sql);
while($obj = $this->db->fetch_object($query)) {
$children = $this->getChild($obj->idPage);
if($children){
$tmp['text'] = $obj->name;
$tmp['id'] = $obj->idPage;
$tmp['leaf'] = false;
$tmp['cls'] = 'folder';
$tmp['children'] = $children;
$nodes[] = $tmp;
}
}
print json_encode($nodes);
}
function getChild($id)
{
# look in sql INNER JOIN user as usu ON usu.idUser = '1' you have to alter it for user logon in the site u can do it by $_SESSION['idUser'] but other way i put 1 for a test
$sql = " SELECT pag.*
FROM page as pag
INNER JOIN user as usu ON usu.idUser = '1'
INNER JOIN permission as per ON (per.idPage = pag.idPage AND per.idProfile = usu.idProfile)
WHERE pag.parent_id = '".$id."' AND pag.visible = '1'
ORDER BY pag.order, pag.name ";
$query = $this->db->query($sql);
while($obj = $this->db->fetch_object($query)){
$tmp['text'] = $obj->name;
$tmp['id'] = $obj->idPage;
$tmp['leaf'] = true;
$tmp['cls'] = 'file';
$nodes[] = $tmp;
}
return $nodes;
}
}
and now our json enode in
get-nodes.php
<?
require_once '../includes/configure.inc.php';
require_once CLASSES.'users.inc.php';
$users = new users;
$users->getParent();
?>
Well, i was looking for a great example about tree menu view with PHP and MySQL and i didnt found a GREAT EXAMPLE so i did it in my self and im here for show u as it is easy :D
so lets start it. This Example its to build a tree menu that check the users permissions and our profiles so the our database look like this
CREATE TABLE profile (
idProfile INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY(idProfile)
)
TYPE=InnoDB;
CREATE TABLE page (
idPage INT(11) NOT NULL AUTO_INCREMENT,
parent_id INT(11) NULL,
name VARCHAR(50) NULL,
url VARCHAR(80) NULL,
action VARCHAR(20) NULL,
visible TINYINT(3) UNSIGNED NULL,
order INT(11) NULL,
PRIMARY KEY(idPage)
)
TYPE=InnoDB;
CREATE TABLE user (
idUser INT(11) NOT NULL AUTO_INCREMENT,
idProfile INT(11) NOT NULL,
name VARCHAR(50) NOT NULL,
login VARCHAR(12) NOT NULL,
password VARCHAR(12) NOT NULL,
accessDate DATETIME NULL,
lastAccess DATETIME NULL,
visits INT(11) NULL,
PRIMARY KEY(idUser),
INDEX user_FKIndex1(idProfile),
FOREIGN KEY(idProfile)
REFERENCES profile(idProfile)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB;
CREATE TABLE permission (
idPage INT(11) NOT NULL,
idProfile INT(11) NOT NULL,
INDEX permission_FKIndex2(idProfile),
INDEX permission_FKIndex1(idPage),
FOREIGN KEY(idProfile)
REFERENCES profile(idProfile)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(idPage)
REFERENCES page(idPage)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB;
Great so u have our databese structure and now you have to insert some records.
i'll explain our database structure so
1. We have a profile table that we insert the users profile if is Admin or its only a simple user
2. We have a page table that we insert the idProfile that check what profile can access this page and etc..
3. We have a user table that we insert a user with ur profile in profile table.
4. and then we have a permission table that define what profile acces page if the user can acces or not! cool ;)
so now lets to the visual part. First create a html file like this:
index.html:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>EXT - Tree View with PHP and MySQL </title>
<link rel="stylesheet" type="text/css" href="../includes/ext/resources/css/ext-all.css" />
<script type="text/javascript" src="../includes/ext/adapter/ext/ext-base.js"></script> <!-- ENDLIBS -->
<script type="text/javascript" src="../includes/ext/ext-all.js"></script>
<script type="text/javascript" src="menu.js"></script>
<style type="text/css">
#tree {
float:left;
margin:20px;
border:1px solid #c3daf9;
width:250px;
height:300px;
overflow:auto;
}
.folder .x-tree-node-icon{
background:transparent url(../includes/ext/resources/images/default/tree/folder.gif);
}
.x-tree-node-expanded .x-tree-node-icon{
background:transparent url(../includes/ext/resources/images/default/tree/folder-open.gif);
}
</style>
</head>
<body>
<div id="tree"></div>
</body>
</html>
and now the 2nd step javascript code:
menu.js:
var TreeTest = function(){
// shorthand
var Tree = Ext.tree;
return {
init : function(){
// yui-ext tree
var tree = new Tree.TreePanel('tree', {
animate:true,
loader: new Tree.TreeLoader({dataUrl:'get-nodes.php'}),
containerScroll: true,
});
// add a tree sorter in folder mode
// set the root node
var root = new Tree.AsyncTreeNode({
text: 'Menu',
draggable:false, // disable root node dragging
id:'source'
});
tree.setRootNode(root);
tree.render();
root.expand(false,false);
}
};
}();
Ext.EventManager.onDocumentReady(TreeTest.init, TreeTest, true);
and now our user class php - ps: i have a extend DB class in my configure.inc.php but its easy for u understand that :D
users.inc.php:
class users {
var $db;
function users()
{
$this->db = $GLOBALS['db'];
}
function getParent()
{
$sql = "SELECT * FROM page WHERE parent_id = 0 ";
$query = $this->db->query($sql);
while($obj = $this->db->fetch_object($query)) {
$children = $this->getChild($obj->idPage);
if($children){
$tmp['text'] = $obj->name;
$tmp['id'] = $obj->idPage;
$tmp['leaf'] = false;
$tmp['cls'] = 'folder';
$tmp['children'] = $children;
$nodes[] = $tmp;
}
}
print json_encode($nodes);
}
function getChild($id)
{
# look in sql INNER JOIN user as usu ON usu.idUser = '1' you have to alter it for user logon in the site u can do it by $_SESSION['idUser'] but other way i put 1 for a test
$sql = " SELECT pag.*
FROM page as pag
INNER JOIN user as usu ON usu.idUser = '1'
INNER JOIN permission as per ON (per.idPage = pag.idPage AND per.idProfile = usu.idProfile)
WHERE pag.parent_id = '".$id."' AND pag.visible = '1'
ORDER BY pag.order, pag.name ";
$query = $this->db->query($sql);
while($obj = $this->db->fetch_object($query)){
$tmp['text'] = $obj->name;
$tmp['id'] = $obj->idPage;
$tmp['leaf'] = true;
$tmp['cls'] = 'file';
$nodes[] = $tmp;
}
return $nodes;
}
}
and now our json enode in
get-nodes.php
<?
require_once '../includes/configure.inc.php';
require_once CLASSES.'users.inc.php';
$users = new users;
$users->getParent();
?>