should i re-query the database every time or use session variables?

[sneakyimp]

should i re-query the database every time or use session variables?

i am developing a content management system that is going to have a hierarchical tree structure to it--kind of like the data tree layout you might see in a Windows help file--as a navigational aid.

depending on what a user might do, this tree could have almost any number of items in it. i'm trying to plan my PHP classes and i'm wondering if it would be smarter to have my PHP pages query the database everytime and construct the tree from scratch or whether i should query once at the beginning of a session and then serialize the tree and store it in a session variable (in which case the server might be pretty heavily taxed memory-wise--or not).

any advice? it also occurred to me that maybe i could use cookies or some kind of client side technique (like frames or cookies or something). HOWEVER, in my experience, frames cause some navigational weirdness with the browser's back button, etc. i'd like to avoid them if possible. also, cookies have a size limit, don't they? and one can't always depend on them being available.

your advice would be much appreciated!

j

[sneakyimp]

i would also like to expand this question slightly to encompass how one might deal with partial parsing of a complete data tree. if the tree is very large, it would be nice if we didn't have to always parse and load the entire thing...only what the user has uncovered so far.

[Weedpacket]

Do both: write session handlers that use the database to store session data. Then you get the advantages of a database (caching in core, indexing, etc.), and the PHP's sessioning functionality (initialisation of $_SESSION[], cleanup, etc.).

It shouldn't be any trouble to store a partial tree in each user's session as a snapshot of what they've seen so far, depending on how mutable the full tree is.

[sneakyimp]

thanks for your reply!

hmmmm.....

i'm having a bit of trouble getting my head around this.

so far i've designed one class in detail--TreeItem. i have routines to add them, delete them (and resquence any siblings of deleted items), getChildren (which gets all child nodes of a node), etc.

i'm thinking that to store a snapshot of a user's tree, i'm going to need a different data structure...like recursively linked data objects or arrays of arrays or some such beastie. my tree item class doesn't have a CHILDREN object...my tree is defined by storing a parentID with each tree item and i can generate the tree with a series of actions like this

TreeRoot = getTreeItemByID('ROOT');

$Children = TreeRoot.GetChildren();

foreach($Children as $Child) {
$Child.Draw();
// or whatever
}

i guess i might have to define a new recursively linkable data object where i can stick all the nodes together in one big fat data object which i can serialize and then store.

...my head hurts.

[sneakyimp]

OK...don't know if anyone gives a hoot, but i'm considering a couple of scenarios for my data tree here and i'm starting to think an efficient way of storing the data would be to define a large Array to hold all my TreeItems, serialize it, and store it in mySQL.

each new visitor to the site would query the database once for the item, unserialize it, and store it in session memory which will keep track of which branches of the tree any particular user has opened.

The advantage of the large array would be that my code can reference a node quickly without any additional queries because the array is indexed on the UIDs of the various TreeItems.

//references might look like this
objTreeArray["3438748372"]->Draw()

I'm worried about the size of this DataArray, though...if every user who visits the site keeps the whole thing in session memory, it could really tax the server--especially if i have a lot of data in each array index.

I want to be able to traverse the tree from top down (easy for drawing the tree) but also from the bottom up (if you click a link, the tree should respond and open up so you can see all the ancestor path of the treeitem you just clicked). i'm considering building another treeitem class called DisplayTreeItem which has some properties like this

$parentid //stores the parent id of the tree item...this helps with bottom-top traversing of tree
$children //array of unique ids of all child tree items in order
$title // the name or text associated with each tree item.
$pageid // the ID of the HTML page or content associated with this tree item that will get displayed i the main pane.
$open //boolean value indicating whether this tree item is open or closed for a given user. closed by default and when there are no child items.

given 10 branches of my tree with 10 items i each branch (for 100 items total), here's a rough estimate of how much memory that might take:
$key <--16 bytes
$parentid <--16 bytes
$children <--between 0 and 160 bytes, depending on #of children...assume 16 average for this tree
$pageid <-- 16 bytes
$open <--1 bit

about 64 bytes per item....that's about 6400 bytes for the whole tree which doesn't sound too bad.

[lazzerous]

sneaky

I had a similar problem, had a three layer menu system that could be updated on the fly.
I had the menu items stored in the database (this also helps out a lot in creating a privilege system)

i have the table for menu items set up nice n simple like so:

+------------+------------------------------------+------+-----+---------+----------------+
| Field      | Type                               | Null | Key | Default | Extra          |
+------------+------------------------------------+------+-----+---------+----------------+
| id         | int(3)                             |      | PRI | NULL    | auto_increment |
| title      | varchar(30)                        | YES  | MUL | NULL    |                |
| link       | varchar(100)                       | YES  |     | NULL    |                |
| type       | enum('parent','child','sub_child') | YES  |     | NULL    |                |
| urlshow    | varchar(15)                        |      |     |         |                |
| urlsubshow | varchar(25)                        |      |     |         |                |
| parent_id  | int(3)                             | YES  |     | NULL    |                |
| position   | int(2)                             | YES  |     | NULL    |                |
+------------+------------------------------------+------+-----+---------+----------------+

here is the code to grab the info from the database and populate it into a multi-dimensional array (I do this in the login script):

		// load menu information from db into a global array
		$get_menu = "select * from admin_menu order by type, parent_id, id, title";
		$do_get_menu = mysql_query($get_menu);

	$menu[parent] = array();
	$menu[child] = array();
	$menu[sub_child] = array();

	while ($row=mysql_fetch_array($do_get_menu))
		{
		switch ($row[type])
			{
			case 'parent':
				$position = $row[position];
				$menu[parent][$position] = array($row[title],$row[link],$row[parent_id],$row[id],$row[urlshow],$row[urlsubshow]);
				break;
			case 'child': 
				$menu[child][] = array($row[title],$row[link],$row[parent_id],$row[id],$row[urlshow],$row[urlsubshow]);
				break;
			case 'sub_child': 
				$menu[sub_child][] = array($row[title],$row[link],$row[parent_id],$row[id],$row[urlshow],$row[urlsubshow]);
				break;
			}
		}

	session_register("menu");

now once this multi-dimensional array is stored as a session variable, i call a function simply called build_menu();

/**
 * @return void
 * @desc - variables provided<br>
 *    - $menu - multi-dimensional array containing all contents of the menu
 *      - this info is pulled for the array from the db at login (login.cinch)
 *      - $menu Structure
 *        - $menu[parent/child/sub_child][#][0] = title - title name to display
 *        - $menu[parent/child/sub_child][#][1] = link - hyperlink to reference to
 *        - $menu[parent/child/sub_child][#][2] = parent_id - id of parent (if child or subchild - for subchild, parent_id is actually the id of the child)
 *        - $menu[parent/child/sub_child][#][3] = id - id of this entry
 *        - $menu[parent/child/sub_child][#][4] = show - defined in this function, and decides what areas of the menu will be visible
 *        - $menu[parent/child/sub_child][#][5] = sub_show - same as $show, but for sub sections (currently only used within 'Products' section
 *    - $show
 *      - $show is defined in this function, and decides what areas of the menu will be visible
 *    - $sub_show
 *      - same as $show, but for sub sections (currently only used within 'Products' section
*/
function build_menu($menu,$show,$sub_show)
	{
	for ($i=0; $i<sizeof($menu[parent]); $i++)
		{
		// echo "parent $i<br>\n";
		if ((isset($show) && $show == $menu[parent][$i][4]) || $show == "all")
			{
			if (eregi("http", $menu[parent][$i][1]))
				{
				echo "<a href='" . $menu[parent][$i][1] . "' target='new'>-</a> &nbsp; <a href='" . $menu[parent][$i][1] . "' target='new'>" . $menu[parent][$i][0] . "</a><br>\n";
				}
			else
				{
				echo "<a href='" . $menu[parent][$i][1] . "?show=" . $menu[parent][$i][4] . "&sub_show=" . $menu[parent][$i][5] . "'>-</a> &nbsp; <a href='" . $menu[parent][$i][1] . "?show=" . $menu[parent][$i][4] . "&sub_show=" . $menu[parent][$i][5] . "'>" . $menu[parent][$i][0] . "</a><br>\n";
				}

		for ($j=0; $j<sizeof($menu[child]); $j++)
			{
			if ($menu[child][$j][2] == $menu[parent][$i][3])
				{
				// echo "child $j<br>\n";
				if (isset($sub_show) && $sub_show == $menu[child][$j][5] || $show == "all")
					{
					if (eregi("http", $menu[child][$j][1]))
						{
						echo "&nbsp; &nbsp; &nbsp; <a href='" . $menu[child][$j][1] . "' target='new'>-</a> &nbsp; <a href='" . $menu[child][$j][1] . "' target='new'>" . $menu[child][$j][0] . "</a><br>\n";
						}
					else
						{
						echo "&nbsp; &nbsp; &nbsp; <a href='" . $menu[child][$j][1] . "?show=" . $menu[child][$j][4] . "&sub_show=" . $menu[child][$j][5] . "'>-</a> &nbsp; <a href='" . $menu[child][$j][1] . "?show=" . $menu[child][$j][4] . "&sub_show=" . $menu[child][$j][5] . "'>" . $menu[child][$j][0] . "</a><br>\n";
						}
					for ($k=0; $k<sizeof($menu[sub_child]); $k++)
						{
						if ($menu[sub_child][$k][2] == $menu[child][$j][3])
							{
							// echo "sub-child $k<br>\n";
							echo "&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <a href='" . $menu[sub_child][$k][1] . "?show=" . $menu[sub_child][$k][4] . "&sub_show=" . $menu[sub_child][$k][5] . "'>-</a> &nbsp; <a href='" . $menu[sub_child][$k][1] . "?show=" . $menu[sub_child][$k][4] . "&sub_show=" . $menu[sub_child][$k][5] . "'>" . $menu[sub_child][$k][0] . "</a><br>\n";
							}
						}
					}
				else
					{
					if (ereg("sawmill", $menu[child][$j][1]))
						{
						echo "&nbsp; &nbsp; &nbsp; <a href='" . $menu[child][$j][1] . "' target='new'>+</a> &nbsp; <a href='" . $menu[child][$j][1] . "' target='new'>" . $menu[child][$j][0] . "</a><br>\n";
						}
					else
						{
						echo "&nbsp; &nbsp; &nbsp; <a href='" . $menu[child][$j][1] . "?show=" . $menu[child][$j][4] . "&sub_show=" . $menu[child][$j][5] . "'>+</a> &nbsp; <a href='" . $menu[child][$j][1] . "?show=" . $menu[child][$j][4] . "&sub_show=" . $menu[child][$j][5] . "'>" . $menu[child][$j][0] . "</a><br>\n";
						}
					}
				}
			}
		}
	else
		{
		if (eregi("http", $menu[parent][$i][1]))
			{
			echo "<a href='" . $menu[parent][$i][1] . "' target='new'>+</a> &nbsp;<a href='" . $menu[parent][$i][1] . "' target='new'>" . $menu[parent][$i][0] . "</a><br>\n";
			}
		else
			{
			echo "<a href='" . $menu[parent][$i][1] . "?show=" . $menu[parent][$i][4] . "&sub_show=" . $menu[parent][$i][5] . "'>+</a> &nbsp;<a href='" . $menu[parent][$i][1] . "?show=" . $menu[parent][$i][4] . "&sub_show=" . $menu[parent][$i][5] . "'>" . $menu[parent][$i][0] . "</a><br>\n";
			}
		}
	}
}

and that's it..

kinda archaic, and I haven't finished notating everything, so I apologize for the messiness of it all

-=Lazz=-

[sneakyimp]

very interesting, lazz...looks a lot like what i'm working on. however, i'm trying to make mine capable of many nested levels so i need to come up with a general/recursive method for parsing everything so i can have parent->child->child->child->child, for instance.

that's what's stumping me on the array thing. i'm thinking i'll define a new class:
class RAMTree (as distinct from TreeItem which deals with database manipulations) which is an array of RAMTreeItems indexed by the UID of each TreeItem. RAMTreeItems might look something like this:

class RAMTreeItem {
var $uid //this might be a bit redundant but very handy at times
var $title
var $parentid
var $children // this will be an array of UIDs
var $open // this indicates whether we show the children of this item or not
}

[lazzerous]

why not just have parent/child0/child1/child2/child3 etc

when creating the m,ulti dim array, just loop through thall the childs, seperate 'child' from the number, and create additional arrays, so you'd end up with

parent/child[0]/child[1]/child[2]/etc

• parent
  • child[0]
    • child[1]
      • child[2]
      • child[3]

however, your concept for the RAMTree class seems entirely feasible

[sneakyimp]

i was trying to make a distinction between multiple children of the same parent and children of children...i'm after a structure like this that can go arbitrarily deep

parent
--child
----grandchild
----grandchild2
--child2
----grandchild
----grandchild2
------greatgrandchild
--------greatgreatgrandchild
--child3

etc.

the idea with RAMTree is that whenever you are given some event regarding any particular treeitem, you can quickly find all its ancestors and its children without additional queries.

[sneakyimp]

ok so the problem i'm having is the recursive struture vs the quick indexed master array.

i'm trying to write a routine to draw a tree and all of its constituent items. if the depth of my tree can be arbitrarily deep, i need a recursive function.

HOWEVER, this sort of flies in the face of my RAMTree array. Although the elements of the RAMTree array do have some references between them, the RAMTree doesn't lend itself well to recursive functions.

i'm thinking maybe i need to either use pointers or declare my RAMTree as a global object so i can reference it from my class definitions. the latter option sounds messy. i anybody have any advice on how i might use pointers in my system here?