Board Logo

MySQL database population
ever0002 - 8/23/2005 at 03:57 PM

I am interested in your product but I can't find any documentation regarding using a db structure to populate the tree menu. In the short term I would like to know how to connect a MySQL db to the tree menu in an Asp.net environment. What kind of db structure must I adhere to in order to use the Tigra Tree Menu with my data?


tigra - 8/23/2005 at 11:31 PM

All you need is to generate the configuration files of the tree menu using your server side script. You can output the text of the configration structure just as you do with the HTML. The database structure and implementation is up to you. Search these forum for the samples in different programming languages. PRO version of the script comes with supported samples in PHP and ASP.


MajorDeBug - 9/15/2005 at 12:03 AM

Don't know if you've found the solution to your problem yet. Had the same challange, as I wanna use the tree menu to make an easily browsable overview over employees per department in each division of a company...

For now, I've done a quick workaround to pospone the hazzle of writing the final code, however, hopefully you'll be able to drw inspiration from the below example.

*Note*
My actual code relies on several of my includes (eg. filehandling class), but since you're gonna implement your code in a .Net environment, you should have no problem figuring the easiest way to approach writing to files etc. In addition, the writing to file is mearly a hack done in order to honor the clauses stipulated in the tree.js and related documentation not to alter code. thus you'll have to alter the "tree_items.js" file on the fly in order to make your tree menu truely dynamic.



//GET DIVISIONS, DEPARTMENT AND EMPLOYEE
//--------------------------------------

// COMMENT
/*
Get yourself a couple of generic counters
*/
// ->
var i, j, k;

// COMMENT
/*
Get a couple of aditional counters to track number of instances
in each loop for each type of group (Div, dept and Emp)
*/
// ->
var divNumber = 0;
var deptNumber = 0;
var empNumber = 0;

// COMMENT
/*
Now in addition to feeding the tree menu the proper data,
I wanna retain my data in several arrays, in order to be able
to perform some additional datajuggling. Thus I'm using several
different arrays to hold all the data.
As you'll be able to see from the below code, I also grab the data
through 3 different recordsets, which pretty much seems to be 'swimming
the pond to get to the water', however, in my case it makes perfect sense.
You decide for yourself if you wanna get your data though one querry
and parse it, or you wanna use the below approach.
*/
// ->
// 3 arrays for Division data and loop tracking
var divID = new Array;
var divName = new Array;
var divSequence = new Array;

// 3 arrays for department data and loop tracking
var deptID = new Array;
var deptName = new Array;
var deptSequence = new Array;

// 3 arrays for employee data and loop tracking
var empID = new Array;
var empName = new Array;
var empSequence = new Array;

// COMMENT
/*
Now seeing that you're not allowed to alter the main code of the tree menu
widget, you wanna walk an alternate path to be able to populate your tree
menu with dynamic data. As stated, I use a QaD hack, just altering the
content of the "tree_items.js". Now - to me the easiest way to achieve that was
to just create a string variable and add to it while running through my DB querries.
I this example the string is simply called: "treeString".
Note that depending on which script or programming language you're using you may
wanna be more specific when declaring your variables...;)
*/
// ->
var treeString = "var TREE_ITEMS = [ ['<INSERT YOUR ROOT NODE NAME HERE>', null,";

// COMMENT
/*
Bla di da - A little SQL:
Getting the division information from the DB
Relies on my DB class. Fetch the dataset from your DB any way you like
*/
// ->
putSQL("SELECT * " +
"FROM " +
"Division " +
"ORDER BY " +
"DIV_Name");

// COMMENT
/*
...And don't forget to get the number of entries in your recordset!!!
Later you'll see why.
*/
// ->
divNumber = oRs.RecordCount;

// Make sure the recordset for divisions actually holds data.
if(divNumber>0) {

// Run through your Division recordset as long as there's entries in it. Note the arrays are '0' based!!!
for(i=0;i<divNumber;i++){

// Now add relevant data to your data arrays
divID = oRs("DIV_ID").value;
divName = oRs("DIV_Name").value;

// Lastly add the necessary content to the "treeString" file.
// Read the tree menu documentation on how to add targets and other data to each
// tree node. In this example, I'll just add "NULL".
treeString+= "['" +divName+ "', null,";

// Now while we're at it, we'll make sure, that for each division, we get all the relevant
// departments as well. Again - the QaD hack way to do this is to querry the DB for relevant departments
// for each time we loop a division, and then write the department information to the treeString
// varriable. In doing so, we'll unfortunately overwrite previous recordsets once we get through
// the loop the second time, and VoilA? - here's the reason that I collect the data in arrays as well.
// This way I'll be able to retain the data for later use in the page...
putSQL(2, "SELECT * " +
"FROM " +
"Department " +
"WHERE " +
"DIV_ID=" +divID+ " " +
"ORDER BY " +
"DEPT_Name");
//...And again - don't forget to get the number of entries in your department recordset!!!
deptNumber = oRs2.RecordCount;

// Again - Make sure the recordset for departments in the relevant division actually holds data.
if(divNumber>0) {

// Deja vu? like for divisions, run through your departments and fill your arrays
for(j=0;j<deptNumber;j++){

// Adding to arrays
deptID[j] = oRs2("DEPT_ID").value;
deptName[j] = oRs2("DEPT_Name").value;

// ...and add your dapartmentdata to the treeString
treeString+= "['" +deptName[j]+ "', null,";

// OK - by now I think we get the picture
// A little SQL the get the employee recordset
putSQL(3,"SELECT " +
"* " +
"FROM " +
"Employee " +
"WHERE " +
"DEPT_ID=" +deptID[j]+ " " +
"ORDER BY " +
"Employee.EMP_FirstName, Employee.EMP_End_Date");

// ... And note the number of employees in the relevant department in the relevant division
// Note that if none are there, we'll just be closing the relevant department furter down.
// I still wanted the departments to show, even if no employees were associated with it yet.
// Should you wanna ommit emply departments, divisions or whatever your using as nodes, you gotta
// add additional code. Sry - I'm a little too lazy after midnight ;)
empNumber = oRs3.RecordCount;

// Still wanna make sure that there's actually employees in the department
// If not, don't execute the code. Don't wory about closing the individual nodes
// just yet though. we'll be doing that later on.
if(divNumber>0) {

// Now for each employee in the department in the division run thorough the loop and...
for(k=0;k<empNumber;k++){

// ... add to your arrays.
empID[k] = oRs3("EMP_ID").value;
empName[k] = oRs3("EMP_FirstName").value+ "&nbsp;" +oRs3("EMP_LastName").value;

// ...and add to your treeString, and you're sort of done...
treeString+= "['" +empName[k]+ "', null,";

//A bit of DB household work: advance one step for each loop in the employee recordset
oRs3.MoveNext();

// ...and NOW the fun begins ;)
// when doing your own code - make sure that you close each of your nodes
// properly. If you don't tree menu will not display AT ALL!
treeString+= "]";

// ...and further more: Make sure to add a "," after each closed employee node
// unless of course it's the last one, then you wanna ommit the ","
// If in doubt, check the "tree_items.js" file included with one of the two demos
// in the tree menu archive. It'll show you the syntax/structure.
if(k<empNumber-1)
treeString+= ",";

// End your employee loop
}

// Close the IF statement that checked to see if there were at all any employees it the department
}

// Don't drain your server resources - remember to close your open recordsets.
oRs3.Close();

// More DB house work. Advance one more step through your department recordset
oRs2.MoveNext();

// Add closing tags to your department nodes
treeString+= "]";

//Add the "," if it's not the last department in the division
if(j<deptNumber-1)
treeString+= ",";

// End your department loop
}

//...and end the IF statement which checked to see if any departments existed in the relevant division
}

// Did i mention to remember to tidy up when you leave the recordset?
oRs2.Close();

// Been there - Done that...
// Advance one step through the divisions
oRs.MoveNext();

// Add the closing tag for divisions
treeString+= "]";

// If not the last division, add the ","
if(i<divNumber-1)
treeString+= ",";

// End the division loop
}

//End the IF statement which checked to see if any divisions were available
}

// AGAIN??? Well - close the last recordset.
oRs.Close();

// Now this is important. Seeing that we manually initiated the treeString with the top level node,
// remember to add the following end tags to the treeString varriable.
treeString+= "] ];";

// well I'm using ASP and jscript, so your code may have to look different.
// However - map the path to the directory where your "tree_items.js" file is located.
var dumpDir = new String(Server.MapPath("<INSERT DIRECTORY WHERE YOUR tree_items.js FILE IS PLACED"));

// Again I've made a class to handle all the nitty bitty details of filehandling functions etc, and named my function
// "iaDumpDataStringToFile". you can do this any way you like, but just overwrite the "tree_items.js" in your dumpDir
// and Vupti - you got a "tree_items.js" file containing the data you need. Now altering your DB data will be
// reflected in your tree menu - neat ;)
iaDumpDataStringToFile(dumpDir+ "\tree_items.js", 2, false, "TristateTrue", treeString)


All that remains is to call the "new tree" js function using the standard:

<script language="JavaScript">
<!--
new tree (TREE_ITEMS, tree_tpl);
//-->
</script>

...and you should be good to go. Remember to include the .css information too, or your tree menu will use default link etc styles.
Nothing altered in the original tree.js file, and you still got what you need. Remember though - js files are executed locally
unless otherwise specified, so using the above approach in a web environment, you wanna make sure that you generate your file
before sending it to the user - Eg. when calling the page holding the tree menu, first call a page to generate the tree_items.js
file on your server, THEN call the page that'll send the info to the user, or any changes made to the tree_items.js file won't
be reflected until you call the page the second time round ;)


Not sure if the above will help you in any way, however, at least it gave me a chance to go through this rather harsh code, and
clear out some of the redundant code. Hey come to think of it: The only thing NOT Quick and Dirty about this was adding these
infernal comments ;)

Cheers.


chale - 5/10/2006 at 11:35 AM

your sample code is really promising. I did't really understand how you overwrite the menu_items.js file. Did you put all your custom code in the file?

Looking forward.

Thank you


Softcomplex: http://www.softcomplex.com/
Back to forum: http://www.softcomplex.com/forum/