TreeView with PHP 5, MySql and Stored Procedures

The time saving performance of Stored Procedures with MySql is well known - they are stored and executed directly on the server. This example of a tree view navigation with Stored Procedures demonstrates this in fact impressively. Even with four Stored Procedures (one cares about the main content, one about the content for the sub tree, one counts all records and one splits the main tree in two corresponding parts), it's only one hundred of a second slower then a XML loop with XPath (see also screenshot below). Besides that XML files are not so easy to maintain as database tables. The Stored Procedures make use of the CONCAT() method and the INSTR() function of MySql. That's all there is to consider. For this tree view you need only two lines to make it work.

The scripts snippets you see here (the complete script is downloadable) is only a small part of the complete application. That's it. All stuff is all prepared for your convenience (example and class-files included). Best regards Claudio Biesele


Part of the TreeView Class Code:

// show top level of tree view
$this->showTreeViewLimitTop($this->calcdigit);
// show content of tree view
$this->showTreeViewContent($this->rpdigit,$this->rp);
// show bottom level of tree view
$this->showTreeViewLimitBottom($this->calcslimit,$this->calcelimit,$this->calcdigit);

 


Part of the Stored Procedure Programming Code:

IF(InRp = '' && InRpDigit = 0) THEN
SELECT nav_id, navterm
FROM tb_treemainnavi
WHERE activ = 1;
ELSEIF(InRp != '' || InRpDigit <> 0) THEN
CALL spShowTreeViewSubNavi(InRpDigit,InRp);
END IF;


Picture screen_sptreeview1.jpg
Example: To create the necessary code you need only two code lines.
Download the full script at www.fastproject.ch. The downloaded script is free from Copyright restrictions, Zurich, 17th of November 2015.
Donate with PayPal:
placeholder