A tree structure means nodes within nodes. By using MySQL and PHP you can read a database then fetch records recursively. By taking an example we can understand it better.
There is a table Category with records for categories and sub-categories. There are 3 columns:
Id, Name, Parent Id and records are:
[php]
1, A, 0
2, B, 0
3, C, 0
4, A1, 1
5, A2, 1
6, A3, 1
7, A4, 4
8, A5, 7
9, B1, 2
10, B2, 9
11, B3, 10
and so on..
[/php]
Depth could be unlimited levels. Here, I have taken only few records and it could goto hundreds and even thousands records.
Pagination for Tree in PHP would be similar like PHP Pagination (refer to my posts on PHP Pagination). For every page say Page Number 1, you need to fetch entire tree (i.e. forming a Tree structure by traversing all nodes) from database and store it in an array.
For the above records, array records would be (storing only Category Ids):
[php]
[0]-> 1
[1]-> 4
[2]-> 7
[3]-> 8
[4]-> 5
[5]-> 6
[6]-> 2
[7]-> 9
[8]-> 10
[9]-> 11
[10]-> 3
[/php]
Now, array would be traversed in this way. For pagination on Page 1 (assuming we are showing 3 records on every page) first 3 records from array would be shown i.e. 1, 4, 7 and on 2nd page next records from array i.e. 8, 5, 6 would be shown and so on..
Issue in this Tree Paging is that for every page you need to first create (or traverse whole tree), store it in an array and display only that part which Pagination asks. To have some efficiency in this method you can modify it a bit (I will explain it after some time).
Till now we have seen records in the one line, but we would like to see them Tabbed. For this we can use ” – ” before each node depending on its level.
Now, 1st page would be shown as:
[php]
1
– 4
– – 7
[/php]
For this kind of display, make your array as 2 d and for each records store Depth as well as follows:
[php]
[0][‘id’] -> 1, [0][‘depth’] -> 1
[1][‘id’] -> 4, [1][‘depth’] -> 2
[2][‘id’] -> 7, [2][‘depth’] -> 3
[3][‘id’] -> 8, [3][‘depth’] -> 4
[4][‘id’] -> 5, [4][‘depth’] -> 2
[5][‘id’] -> 6, [5][‘depth’] -> 2
[6][‘id’] -> 2, [6][‘depth’] -> 1
[7][‘id’] -> 9, [7][‘depth’] -> 2
[8][‘id’] -> 10, [8][‘depth’] -> 1
[9][‘id’] -> 11, [9][‘depth’] -> 3
[10][‘id’] -> 3, [10][‘depth’] -> 1
[/php]
Now, we can change it slightly to have a better look. When showing a record we can view its depth and then call a function to print ‘ – ‘ x times where x is equal to Depth – 1 for that record.
Now, in this case what will happen is when we are on Page 2, we will see records starting from 8 like this:
[php]
– – – 8
– 5
– 6
[/php]
It is not clear now that what is parent of 8. For this we can again call a function to print all parents of 8 (which are 7, 4, 1) using ‘ – ‘.
Then pattern would be:
[php]
1
– 4
– – 7
– – – 8
– 5
– 6
[/php]
In such a case, paging with 3 records on each page are being shown along with Parents of the Top most Record.
About some level of efficiency, as I mentioned it earlier that we need to fetch whole tree for every page, but we can have some level of efficiency with 1st page having maximum efficiency and last page having the least. This can be done as follows:
We know that how many records we are showing on each page. So, when we are creating the array to store Category Id and Depth we can check if the count of array is greater than equal to (Current PageNumber * Number of Records on Each Page) i.e. records till now then return and the function will return recursively and computation of further tree won’t be done and hence some efficiency would be achieved. Lets take an example:
When we are on page 1, there are 3 records to be shown. We will start building our array. There would be Record 1 with Ctg. Id: 1, Record 2 with Ctg. Id: 4 and then Record 3 with Ctg. Id: 7 (we are checking count of Array after each step) at this time Count is 3. As it is = 1*3 (i.e. Current PageNumber * Number of Records on Each Page) we will return and show results.
For page 2, it would be Ctg. Id: 1, Ctg. Id: 4, Ctg. Id: 7, then Ctg. Id: 8 (count is 4) then Ctg. Id: 5 (count is 5) then Ctg. Id: 6 (count is 6) and it is now = 2*3 (i.e. Current PageNumber * Number of Records on Each Page). So, we will return.
The efficiency for page 2 is that it doesn’t need to traverse tree beyond 6 records. With this way you will find efficiency decreases with each increasing page number.
You can view a demo on this page: http:/smstongue.com/index.php?page=d/ctg/tree_paging