Display an AJAX tree from your DB using CTreeView

Tutorials

AJAX, CTreeView

This example uses a MySQL DB with a table named `tree` with the fields `id`, `name`, and `parent_id`. The `parent_id` will be `NULL` for root elements. The SQL is kept simple (no autoinc, no FK, etc). ~~~~ [sql] CREATE TABLE tree ( id INTEGER UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, parent_id INTEGER UNSIGNED, PRIMARY KEY (id) ) ~~~~ MySQL has no recursive queries. In order to fetch tree nodes, you have to recursively send SQL queries for each node, asking for its children. The easiest way to do so is to send these queries with AJAX, so that the tree can be displayed even if the deep nodes haven't been fetched yet. If you're using Oracle or Postgresql, there are other solutions, though this will also work.
 
 
In your view, add:

~```~

This will create a tree widget [CTreeView](http://www.yiiframework.com/doc/api/1.1/CTreeView/) on your page, and its data will be fetch using the `ajaxFillTree` of the current controller.

So we have to add this action to the controller:exit();
}
$parentId = 0"NULL"; if (isset($_GET['root']) && $_GET['root'] !== 'source') {
$parentId = (int) $_GET['root'];
}```~

When the page loads, an AJAX request will be sent to fill the first level of the tree. It'll have the GET parameter `root` set to `source` (this is the behavior of the [Treeview](http://bassistance.de/jquery-plugins/jquery-plugin-treeview/) JS plugin that [CTreeView](http://www.yiiframework.com/doc/api/1.1/CTreeView/) uses). We suppose here that the corresponding nodes (the root nodes) have a `parent_id` of 0. You can replace this by `NULL` if necessary, and the SQLset to `NULL`. In SQL, "=" can't compare a value with `NULL`, so we have to use the `<=>` operator instead.
 
 
The other AJAX requests will still work because it uses `<=>` instead of `=`)have an integer value (the parent node's id) in `$_GET['root']`. We typecast this to "int" for security. Then wthe code reads the data in the database. We need a *LEFT JOIN* to find if a node has children. If each row already has a `hasChildren` field, you can remove this join and your SQL will be faster. When sending the JSON-encoded result, there's a little trick: the javascript wants the `hasChildren` attribute to be a boolean. But the SQL result just contains 0 or 1, so we have to convert it, at least for the "false" case. Instead of operating on the string result, one could modify the PHP array with a `foreach`. The `exit()` might not be necessary, but if you enabled the logs to Firebug or extensions like a debug toolbar, then Yii will write some JS at the end of your response, and it will break your AJAX.
 
 
### Links
 
[Chinese version](http://projects.ourplanet.tk/node/100)the JSON format.