How can I create tree table by using AJAX that load 1 level at a time?

Question

I'm still unable to make treetable as my expectation. My data is from mysql database with 8 level of hierarchy and thousands of records.

I followed the answer posted by bcoughlan to question : How to create a collapsing tree table in html/css/js? and use data from my database.

It works very well, but :

It tooks very long time to load the table in the beginning, as my AJAX still loop all data from database in the beginning (I don't have knowledge about this, only copy and trial).
I can make the tree colapsed at the first time by using style="display: none;" but when I click any node from that 1st level, it expands all levels below that node (2nd until 8th level) instead of expanding the 2nd level only.

I still cannot figure out how to make AJAX script for the above 2 concerns, to make the page loading time shorter in the beginning of the page.

UPDATE PROGRESS :

Now I can make the AJAX works for the 2nd level.

Still trying although dont have idea how to make it to the 3rd level until 8th level. :)

My script now is as followed, while trying to the 3rd level:

Main.php

<html>
<head>
    <style type="text/css">
        .name               {   color: #000000;         }
        .folderclose        {   height: 16px;
                                width: 16px;
                                display: inline-block;  }
        .area .last         {   background-image: url(02_files/01_images/paper_small.png);          }
        .folderclose        {   background-image: url(02_files/01_images/folder_green.png);         }
        .expand .tog        {   background-image: url(02_files/01_images/folder_green_open.png);    }
    </style>
    <script type="text/javascript" src="jquery-2.1.4.js"></script>
</head>
<body>
    <form>
    <table>
        <tr><th>No</th>
            <th>Area & Plant</th>
            <th>Location</th>
            <th>Main Group</th>
            <th>Sub Group</th>
            <th>Function Group</th>
            <th>Functional Location</th>
            <th>Equipment</th>
        </tr>
<?  mysql_connect("localhost","root","");
    mysql_select_db("sfer");
    $areaid = 1;
    $sql = mysql_query("SELECT DISTINCT(Business_Area) FROM b01_functional_location");
    while($data=mysql_fetch_array($sql)){?>
        <tr id="area">
            <td align='center' class="id"><?=$areaid?></td>
            <td align='left' width='400'>   <a class="folderclose"></a>
                                            <a title='<?=$areaid.'. '.$data['Business_Area']?>' href="javascript:void(0)" class="name"><?=$data['Business_Area']?></a></td></tr>
        <tr id="plant<?=$areaid?>"></tr><?
        $areaid++; }

if (!empty($_POST['area'])){
    $plantid = 1;
    $sql = mysql_query("SELECT DISTINCT(Plant) FROM b01_functional_location WHERE Business_Area='$_POST[area]'");
    while($data=mysql_fetch_array($sql)){?>
        <tr id="plant">
            <td align='center' class="id"><?=$plantid?></td>
            <td align='left' width='400'>   <a class="folderclose"></a>
                                            <a title='<?=$plantid.'. '.$data['Plant']?>' href="javascript:void(0)" class="name"><?=$data['Plant']?></a></td></tr>
        <tr id="location<?=$plantid?>"></tr><?
        $plantid++; }}?>

        </table>
    </form>
</body>
<script type="text/javascript">
$('document').ready(function(){
    $('#area .name').click(function() {
        var area = $(this).text();
        var areaid = $(this).parent().parent().find('.id').text();
        $('#plant'+areaid).html('<img src="02_files/01_images/loading.gif">');
        $.ajax({
            type:   'POST',
            url:    'Action.php',
            data:   'area='+area+'&areaid='+areaid,
            success: function(data){
                $('#plant'+areaid).html(data).toggle();
            }
        });
    });
    $('#plant .name').click(function() {
        var plant = $(this).text();
        var plantid = $(this).parent().parent().find('.id').text();
        $('#location'+plantid).html('<img src="02_files/01_images/loading.gif">');
        $.ajax({
            type:   'POST',
            url:    'Action.php',
            data:   'plant='+plant+'&plantid='+plantid,
            success: function(data){
                $('#location'+plantid).html(data);
            }
        });
    });
});
</script>

Action.php

<?php
mysql_connect("localhost","root","");
mysql_select_db("sfer");

if (!empty($_POST['area'])){
$plantid = 1;
$sql = mysql_query("select DISTINCT(Plant), Plant_Description from b01_functional_location where Business_Area='$_POST[area]'");
    while($data=mysql_fetch_array($sql)){?>
        <tr id="plant">
            <td align='center' class="id"><?=$plantid?></td>
            <td align='left' width='400'>   <a class="folderclose"></a>
                                            <a title='<?=$plantid.'. '.$data['Plant']?>' href="javascript:void(0)" class="name"><?=$data['Plant']?></a></td>
            <td align='left'><?=$data['Plant_Description']?></td>
        </tr><? $plantid++;}    }

if (!empty($_POST['plant'])){
$locationid = 1;
$sql = mysql_query("select DISTINCT(Location), Location_Desc from b01_functional_location where Plant='$_POST[plant]'");
    while($data=mysql_fetch_array($sql)){?>
        <tr id='location'>
            <td align='center' class="id"><?=$locationid?></td>
            <td align='left' width='400'>   <a class="folderclose"></a>
                                            <a title='<?=$locationid.'. '.$data['Location']?>' href="javascript:void(0)" class="name"><?=$data['Location']?></td>
            <td align='left'><?=$data['Location_Desc']?></td>
        </tr><? $locationid++;} }

Need your help.

I suggest you ask two questions for this. There are a couple of options for your first problem. One of the reasons it is taking so long is that you are calling the database in a loop. This is inefficient. Look at using a stored procedure to make on database call and return a dataset that includes hierarchy information and work with that in a single loop. — Jon P, Apr 23 '15 at 23:20
The other option is to load the first tier only and use ajax to populate the rest on demand. — Jon P, Apr 23 '15 at 23:27
@JonP: Question editted. Need your further explanation about stored procedure on database call. I also don't have knowledge about AJAX, is it possible only using html/css/js? Really appreciate. — anesbbs, Apr 24 '15 at 00:25
The J in ajax is for javascript and that is how you would handle partially loading the table asynchronously (the first A in Ajax). So without full page post backs that is the only way to partially load the table. — Jon P, Apr 24 '15 at 00:55
Question editted. Need help on Ajax, with or without Jquery. — anesbbs, Sep 30 '15 at 09:12
Editted again after modifying the script to use jquery code with ajax request. FYI, first load only show 1st level because I put style="display:none;" since 2nd level. But actually it is still loading the 2nd - 8th level from the beginning. — anesbbs, Oct 01 '15 at 03:09
If you want us to fix your AJAX code.... you'll need to post your code. Is your ajax code still attacking the database in a row by row loop? You need to alter it so that it gets back _all_ of the records in one dataset, not repeatedly go back and forth to get the data. — Nick.Mc, Oct 30 '15 at 06:35
I know it has ajax request in it.. but can you provide a fiddle for this? — LiranBo, Oct 30 '15 at 09:14
I'm an amateur in coding & not familiar with fiddle. Is it possible to simulate dynamic code in fiddle? I mean where to put the database table? — anesbbs, Nov 06 '15 at 04:20
@LiranBo, this is another script I saved in [jsfiddle](https://jsfiddle.net/anesbbs/jnuhLocw/2/). Need your input please. — anesbbs, Oct 20 '16 at 08:32
@JonP, do you have example or fiddle about using AJAX to populate rest of the level on demand that I can follow? Really appreciate if any. — anesbbs, Oct 20 '16 at 08:48

How can I create tree table by using AJAX that load 1 level at a time?

0 Answers0