How to get data to function(d) and out of that function.

culter

I'm using DataTables with server-side processing to show data from mysql table. This part works fine. I also show some additional data after click on table row. These data are based on the id of the clicked row. The additional data from another table are grabbed by php script like this:

<?php
$q = intval($_GET['q']);

$conn = mysqli_connect("10.10.99.11", "abc", "def", "tv");

$result = mysqli_query($conn, "SELECT TIME, ADESC FROM TM, AT WHERE TM.ACTION=CWERT AND SPRAS='EN' AND NO= '" . $q . "'");

// storing in array
$workm = array();
while ($row = mysqli_fetch_assoc($result))
{
        $workm[] = $row;
}

// returning response in JSON
echo json_encode($workm);

$conn->close();
?>

My index.php

<!-- this table works ok -->
<table><thead>
<tr><th>TIME</th><th>ADESC</th></tr>
</thead>
<tbody id="workm"></tbody></table>


<script>
    function format ( d ) {
    return '<div id="rowdetails">'+
                '<div class="row">' +

                        '<div class="col-4">' +
                                '<div class="row">' +
                                        '<div class="col-6">' +
                                                '<span class="be">' + d[0] + '</span>' +
                                        '</div>' +
                                        '<div class="col-6">' +
                                                d[5] + '<br>' +
                                                d[7] +
                                        '</div>' +
                                '</div>' +
                                'Issue: ' + '<div class="issuetext">' + d[6] + '</div>' +
                        '</div>' +

                        '<div class="col-4">' +
                                '<table><thead><tr><th>Class</th><th>Work</th><th>Err</th></tr></thead>' +
                                        '<tbody><tr><td></td><td>' + d[4] + '</td><td>' + d[8] + '</td></tr></tbody>' +
                                '</table>' +
                        '</div>' +

                        '<div class="col-4">' +
                                'Site: ' + d[1] + '<br>' +
                                d[10] + '<br>' +
                                d[11] + '-' + d[12] + ' ' + d[2] +
                        '</div>' +
                '</div>' +

'<table><thead><tr><th>TIME</th><th>ADESC</th></tr></thead>' +
        '<tbody id="workm"></tbody></table>' +
           '</div>';
}



var sample = 900055;

    var ajax = new XMLHttpRequest();

    ajax.open("GET","data.php?q="+sample, true);
    // sending ajax request
    ajax.send();

    // receiving response from data.php
    ajax.onreadystatechange = function()
    {
            if (this.readyState == 4 && this.status == 200)
            {
                    // alert(this.responseText);
                    // converting JSON back to array
                    var workm = JSON.parse(this.responseText);
                    console.log(workm);

                    // html value for <tbody>
                    var html = "";
                    // looping through the data
                    for (var a = 0; a < workm.length; a++)
                    {
                            var time = workm[a].TIME;
                            var measure = workm[a].ADESC;

                            // appending at html
                            html += "<tr>";
                                    html += "<td>" + time + "</td>";
                                    html += "<td>" + measure + "</td>";
                            html += "</tr>";
                    }
                    // replacing the <tbody> of <table>
                    document.getElementById("workm").innerHTML = html;
            }
}
</script>

The table outside the function is working ok.

I have two problems:

1. The table with the additional data is not working in the function(d)
2. In the function(d), the variable d[0] contains id which I need to push to ajax call instead of sample.

Thank you for any advice.

allan

Hi,

Have a look at this blog post which shows how you can Ajax load child details for a row.

Allan

culter

Hi allan, I'm already using this. Problem is, that I need data from 3 tables. Your advice in other thread was, that the best way is to create new view with data from all 3 tables, which is great, but I have "one to many" relationship between the tables, so I have several rows in the second table to one row in the main table.

And the problem is, that I need to display only unique rows in the main view and after click on the row I need the additional data to be displayed. Is this possible? Thank you very much.

allan

Your advice in other thread was, that the best way is to create new view with data from all 3 tables

That is true if you want to load all of that information at the same time. If you can load information from the many join tables at a later point (e.g. when the child row is shown), then you could just read the main table as normal and have the Ajax call get the "many" data based on the parent row's id.

I think the issue I'm having here is that I don't know what the data structure is or what it is trying to represent. If your main table is displaying only unique rows, what makes them unique? An id? If so, what links to the "many" - that same id? If that is the case, can you just load the "many" rows from the Ajax call based on that id?

Allan

culter

Hi allan,

I just modified server_processing.php and ssp.class.php to work with more tables, so now I'm able to get data from another tables and make JOIN. The structure is (only 2 tables to be more simple):

MySQL Table 1 (I'm loading 15 columns from this table, 6 of them are displayed in the main table, others are displayed in row details - this works ok)
id time work measure .....
1 22:34 1 342
2 11:15 1 454
3 12:00 0 0
....

MySQL Table 2 (to every id from table 1, here is several actions, which I need to display in row details)

id action
1 56
1 78
1 96
2 11
...

So now I get in the main table this (column action is displayed only in row details)

id time work measure .....
1 22:34 1 342
1 22:34 1 342
1 22:34 1 342
2 11:15 1 454
....

I need to display only unique id's in the main table, but also keep all the actions from the second table to be displayed after click on the row.

Hope it's more clear now. Thank you.