Guriddo » Topic: Creating TreeGrid from multiple tables

Creating TreeGrid from multiple tables

Guriddo
treegid
Creating TreeGrid from multiple tables

Tagged: treegid

This topic has 6 replies, 2 voices, and was last updated 7 years, 11 months ago by abhinav30.

Creator

Topic
July 1, 2015 at 4:28 am #123980

abhinav30
Participant

Hello,

I have database with following three tables (for each level )

Parent (ID=parent_id, Parent=None)

Child1(ID=Child1_id,Parent = parent_id or None for some cases)

Child2(ID=Child2_id,Parent = Child1_id or None for some cases)

Can somebody suggest me how to use these tables in TreeGrid.

As the rows in those tables are huge, so I don’t want everything to be load at once., I would like to use the “autoloading” feature so that when user click on any node, then based on the node clicked, I want to query the database on fly and display.

Thanks
Creator

Topic

Viewing 6 replies - 1 through 6 (of 6 total)

Author

Replies
July 1, 2015 at 11:40 am #123984
guriddo.support
Participant
Hello,

I think that this can be done if I understand right the task.

The trick is to use the level posted from user when they want to expand the node.

Using the approach described in the documentation you can easy select the needed table.

Bellow is a idea in PHP of how this can be done
```
$node = (integer)$_REQUEST["nodeid"];
$n_lvl = (integer)$_REQUEST["n_level"];

$table = 'Parent';

// Suppose the root level is 0 (see docs for config)
if($n_lvl == 0) {
   $wh = 'ISNULL(parent_id)'; // roots
} else if ($n_lvl == 1 ) {
   $wh = 'parent_id='.$node; // parents
   $table = 'Child1';
} else if(...) {
   ...
}
 
$SQL = "SELECT ... FROM ".$table." WHERE ".$wh;
 
```
I think you got the idea.

Kind Regards,
Will

Guriddo Support Team
July 2, 2015 at 7:43 am #123985
abhinav30
Participant
Hello,

Thanks for the response.I will give it a try.

Started with my first grid. Data connection is OK. I can see data fetched from database in json format but still my grid is blank.

Data displayed in jason as below:

{“records”:0,”page”:0,”total”:0,”rows”:[{
“ID”:1,”parent”:”0″},{“ID”:2,”parent”:”0″}]}

Table Information :

Name : parent

Column : ID,parent

Rows : (1,0)(2,0)

Here is php code :

<?php
ini_set(“display_errors”,1);

require_once ‘../jq-config.php’;
// include the driver class
require_once ABSPATH.”php/PHPSuito/DBdrivers/jqGridPdo.php”;
// include the jqGrid Class
require_once ABSPATH.”php/PHPSuito/jqTreeGrid.php”;
// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query(“SET NAMES utf8”);
// Create the jqTreeGrid instance
$tree = new jqTreeGrid($conn);

$tree->SelectCommand = “SELECT * FROM parent”;

// set the table and primary key
$tree->table = ‘parent’;
$tree->setPrimaryKeyId(‘ID’);
// set tree model and table configuration
$tree->setTreeModel(‘nested’);
$tree->setTableConfig(array(‘id’=>’ID’, ‘left’=>’null’, ‘right’=>’null’,’level’=>’0′));

// autoloading nodes
$tree->autoLoadNodes = true;
// show any error (if any ) from server
$tree->showError = true;

$tree->setColModel();

$tree->setUrl(‘mygrid.php’);
$tree->dataType = ‘json’;
// Some nice setting
$tree->setColProperty(‘ID’,array(“label”=>”ID”, “width”=>170));
$tree->setColProperty(‘parent’,array(“label”=>”parent”, “width”=>170));

// and finaly set the expand column and height to auto
$tree->setGridOptions(array(
“ExpandColumn”=>”ID”,
“height”=>”auto”,
“sortname”=>”ID”
));
$tree->renderTree(‘#tree’, ‘#pager’, true,null, null, true, true);
- This reply was modified 7 years, 11 months ago by abhinav30.
July 2, 2015 at 9:36 am #123987
guriddo.support
Participant
Hello,

1. I think that you are not in nested model, but rather in Adjacency tree model
So you will need to replace

$tree->setTreeModel('nested');

with

$tree->setTreeModel('adjacency');

2. In your case it is better to write your own wrapper, rather than to use the jqGrid TreeGrid PHP one – this is just my opinion.

Please look carefully in the link that in provide you in my previous post.

Kind Regards,
Will
- This reply was modified 7 years, 11 months ago by guriddo.support.
- This reply was modified 7 years, 11 months ago by guriddo.support.
Guriddo Support Team
July 2, 2015 at 8:25 pm #123993
abhinav30
Participant
Hello,

I have tried both option

Option 1 : I have replaced it with $tree->setTreeModel(‘adjacency’); but still my TreeGrid is empty.

Option 2 : I tried the fresh start , I have created two new files with following content but nothing show up.

treegrid.php
```
<!DOCTYPE html>
<html>
<head>
<title>Guriddo TreeGrid PHP Demo</title>
<link rel="stylesheet" type="text/css" media="screen" href="../../../css/jquery-ui.css" />
<link rel="stylesheet" type="text/css" media="screen" href="../../../css/trirand/ui.jqgrid.css" />
<style type="text">
html, body {
margin: 0; /* Remove body margin/padding */
padding: 0;
overflow: hidden; /* Remove scroll bars on browser window */
font-size: 75%;
}

</style>
<script src="../../../js/jquery.min.js" type="text/javascript"></script>
<script src="../../../js/trirand/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="../../../js/jquery-ui.min.js" type="text/javascript"></script>
<script src="../../../js/trirand/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
$.jgrid.no_legacy_api = true;
$.jgrid.useJSON = true;
$.jgrid.defaults.width = "700";
</script>
</head>
<body>
<div><?php include "myfirsttreegrid.php";?></div>
</body>
</html>
```
myfirsttreegrid.php
```
<?php
ini_set("display_errors",1);
require_once '/jq-config.php';

// include the driver class
require_once "php/PHPSuito/DBdrivers/jqGridPdo.php";
// include the jqGrid Class
require_once "php/PHPSuito/jqTreeGrid.php";

// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");
// Create the jqTreeGrid instance
$tree = new jqTreeGrid($conn);

$tree->SelectCommand = "SELECT * FROM parent";

// set the table and primary key
$tree->table = 'parent';
$tree->setPrimaryKeyId('ID');
// set tree model and table configuration
$tree->setTreeModel('adjacency');
$tree->setTableConfig(array('id'=>'ID', 'parent'=>null));

// autoloading is disabled
$tree->autoLoadNodes = false;
// collapse all nodes (default)
$tree->expandAll = true;
// show any error (if any ) from server
$tree->showError = true;

$tree->setColModel();

$tree->setUrl('treegrid.php');
$tree->dataType = 'json';
// Some nice setting
$tree->setColProperty('ID',array("label"=>"ID", "width"=>170));
$tree->setColProperty('parent',array("label"=>"parent", "align"=>"right","width"=>90));
// and finaly set the expand column and height to auto
$tree->setGridOptions(array(
// set which column should act as expanded one
"ExpandColumn"=>"ID",
"height"=>'auto',
"sortname"=>"ID",
// allow automatic scrolling of the rows
"scrollrows"=>true
));
// enable key navigation
$tree->callGridMethod('#tree', 'bindKeys');

$tree->navigator = true;
$tree->setNavOptions('navigator', array("add"=>true,"edit"=>true, "del"=>true, "search"=>false, "excel"=>false));
$tree->renderTree('#tree', '#pager', true,null, null, true, true);
?>
```
Thanks and Regards

Hemadri
- This reply was modified 7 years, 11 months ago by abhinav30.
- This reply was modified 7 years, 10 months ago by tony. Reason: code formatting
July 2, 2015 at 9:15 pm #123995

abhinav30
Participant

sorry missed the data..

Now data which I am getting is

{“userdata”:[],”rows”:[{“ID”:”1″,”parent”:null,”level”:0,”isLeaf”:”true”,”loaded”:”true”,”expanded”:”true”},{“ID”:”1″,”parent”:null,”level”:0,”isLeaf”:”true”,”loaded”:”true”,”expanded”:”true”}],”total”:2,”page”:1}

July 2, 2015 at 10:54 pm #123996

abhinav30
Participant

I was able to make it work but it worked only once and now its giving me the following error :

<br />
<b>Fatal error </b>: Allowed memory size of 134217728 bytes exhausted (tried to allocate 65488 bytes) in <b>C:\xampp\htdocs\Guriddo_Suito_PHP_4_8_2\php\PHPSuito\jqTreeGrid.php</b> on line <b> 5 </b> <br />

Now coming back to real problem.

For the table “Parent” mention below, “Parent” field is defined as “NULL” so it is being treat as a leaf node but actually its got child which are defined in “Child” table (ID 101 and 102). Do I need to fake it somehow ?

Table Parent :

ID | Parent

———————

1 | NULL

2 | NULL

Table :Child

ID | Parent

———————

1 01 | 1

102 | 1

201 | 2

301 | NULL
Author

Replies