Server-Side Example SSP Class Working (but adding edit/delete button issues)

Server-Side Example SSP Class Working (but adding edit/delete button issues)

chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

Intro: I am doing a server-side datatable.net Jquery plug-in using json, ajax, and ssp.class.php. I have it working but attempting to make buttons that can edit and delete. I am able to do this without the plug-in (but for some reason am stumped currently).

Output Link: Sample Link:

Code:

       $(document).ready(function() {
            $('#example').DataTable( {
               // "pagingType": "scrolling",
                "processing": true,
                "serverSide": true,

                    "ajax": {
                        "url": "server.php",
                        "type": "POST"
                    },
             });

         }); 
        </script>
          <body>

        <table id="example" class="display" style="width:100%" class="table table-striped table-bordered table-hover table-condensed">
         <thead class="thead-inverse">
        <tr>
        <th><a class="column_sort" id="id" href="?order=id&sort=<?php echo $sort; ?>">
                ID
        </th>
        <th><a class="column_sort" id="first_name" data-order="<?php echo $sort;?>" href="?order=first_name&sort=<?php echo $sort; ?>">First Name </a>
        </th>
        <th><a class="column_sort" id="last_name" href="?order=last_name&sort=<?php echo $sort; ?>">Last Name
        </a>
        </th>
        <th><a class="column_sort" id="position" href="?order=position&sort=<?php echo $sort; ?>">Position
        </a>
        </th>
        <th class="hidden-xs"><a class="column_sort" id="date" href="?order=date&sort=<?php echo $sort; ?>">Date </a>
        </th>
        <th class="hidden-xs"><a class="column_sort" id="updated" href="?order=updated&sort=<?php echo $sort; ?>">Updated
        </a> </th>
        <th>Action</th>
        </thead> </tr>
                <tbody>

                </tbody>
            </table>
            </div>          
        <?php

        $orderby="";
        $sort="";
        $sort = isset($_GET['sort']) ? $_GET['sort'] : 'ASC';
        $sort = ($sort == 'ASC') ? 'DESC': 'ASC';

        $order  = array("first_name","last_name", "date", "position", "updated"); 
        $key     = array_search($sort, $order); 
        $orderby = $order[$key];
        $records = mysqli_query($con, "SELECT * FROM employees ORDER BY $orderby $sort");

        $data=array();
        while ($row = mysqli_fetch_array($records, MYSQLI_ASSOC)) { 
            $row['delete_button']='<button type="button" class="btn btn-warning">Delete</button>';
            $data[]=$row;
        }
        $requestData= $_REQUEST;

        $count=mysqli_query($con, "SELECT * FROM employees"); 
        $totalData= $count->num_rows;
        $totalFiltered=$totalData;

        $json_data = array(
                          "draw" => intval(isset($_GET['draw'])), 
                          "recordsTotal"    => intval( $totalData ), 
                          "recordsFiltered" => intval( $totalFiltered ),
                          "data"            => $data //How To Retrieve This Data
                         );

        echo json_encode($json_data);
        ?>

Error:
DataTables warning: table id=example - Requested unknown parameter '6' for row 0, column 6. For more information about this error, please see http://datatables.net/tn/4

This question has an accepted answers - jump to answer

«1

Answers

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    HERE IS SAMPLE LINK: https://databasetable-net.000webhostapp.com/ The one in the main post is the wrong link. I cannot edit, I think it has been after the 15min period. Sorry!

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    The link says it is "sleeping". I'll try and get in a little while.

    Allan

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1
    edited April 2018

    Yes it sleeps for 1 hour at 3am eastern time. You either do not live in the USA or you up very early (ha). As of right now I have it on a free server temporarily. Sorry for the trouble.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I'm in the UK :).

    Try adding something like:

    columnDefs: [ {
      targets: -1,
      defaultContent: 'Edit / Delete' // expand as needed
    } ]
    

    to your initialisation of the DataTable. This example shows that being done in an Editor context, but the same method will work for any.

    Allan

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    The editor link you gave looks like the below. So use either or depending on which one I want? A little confused.

        columns: [
            { data: null, render: function ( data, type, row ) {
                // Combine the first and last names into a single table field
                return data.first_name+' '+data.last_name;
            } },
            { data: "position" },
            { data: "office" },
            { data: "extn" },
            { data: "start_date" },
            { data: "salary", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) },
            {
                data: null,
                className: "center",
                defaultContent: '<a href="" class="editor_edit">Edit</a> / <a href="" class="editor_remove">Delete</a>'
            }
        ]
    } );
    

    } );

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    ok maybe this is the one i need:

    $('#myTable').DataTable( {
    ajax: ...
    } );

    // or!

    $('#myTable').DataTable( {
    ajax: ...,
    columns: [
    { data: 0 },
    { data: 1 },
    { data: 2 },
    { data: 3 },
    { data: 4 },
    { data: 5 }
    ]
    } );

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    I think my solution needs to be similiar to this link: https://m.datatables.net/forums/discussion/39141/solved-how-to-add-data-c-column-as-a-variable-in-defaultcontent

    columns: [
    {data: "c.name"},
    {data: "c.contact"},
    {data: "c.phone"},
    {data: "c.fax"},
    {data: "c.email"},
    {data: null, defaultContent: "<span class='bg-linkedin rounded sq-24'><span class='icon icon-eye'></span></span><span class='bg-linkedin rounded sq-24'><span class='icon icon-pencil'></span></span><span class='bg-googleplus rounded sq-24'><span class='icon icon-remove'></span></span>"}
    ]

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    Sounds like you are asking whether to define your buttons using columns or columnDefs. The answer is "Yes" :wink: You can use either depending on how you want to organize the config options. In general the columnDefsoption is useful when you want to provide the same configuration for multiple columns or you are not using columns.

    Kevin

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    Yes I suppose that helps. This solution is interesting, looks like they used both? https://forums.asp.net/t/2120137.aspx?Jquery+Datatable

    columns:[
    {data: "id"},
    {data: "name"},
    {data: "ip"},
    {data: "description"},
    {data: null} //this is index 4
    ],
    columnDefs:[{
    targets: 4,
    render: function (data, type, row, meta) {
    return '<a type="button" class="btn btn-danger btn-block" href="#" onclick=delFromID(' + row.id + ') >删除</a>';

    Why target: -1? Is that a silly question?
    Thanks!

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    **SOLUTION! **
    https://datatables.net/reference/option/columns.defaultContent

    "columnDefs": [
    {
    "data": null,
    "defaultContent": "<button>Edit</button>",
    "targets": -1
    }
    ]

    Thanks. I may have more questions later...

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    You can use both. You just need to be aware of the config priority between columns and columnDefs:
    https://datatables.net/reference/option/columnDefs#Conflict-resolution

    There are a few options available for columnDefs.targets. One of them is negative numbers, counting from the right.

    Kevin

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1
    edited August 2018

    How do I add a second, button delete? When I try to, it messes up the entire website.

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    What have you tried? Do you get any console errors?

    There are many ways to do this depending on how you want the buttons to work.

    Kevin

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    actually i was thinking about it while doing something else and realized how to do it. i was overthinking.

    <button>Edit</button> <button>delete</button>

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    ...getting the buttons to be clickable is going to be tougher. This is how I did it without datatables.net plugin. May take me a while... let me know if you have any suggestions. Thanks!

    <a href="edit.php?edit=<?php echo $row['id']; ?>" name="edit" class="button green_btn"><span class="glyphicon glyphicon-pencil"> </a>
    <a href="index.php?del=<?php echo $row['id']; ?>" name="del" class="button del_btn" onclick="return confirm('Are you sure you want to delete this item?');"><span class="glyphicon glyphicon-trash"></span> </a>

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    I put this simple example together:
    http://live.datatables.net/xijecupo/1/edit

    It uses $(this).closest('tr'); to get the row. The uses row().data() to get the data for that row.

    Kevin

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    Ok I am looking over the examples and links in great detail. I am doing server-side processing but I think the foundation is still going to mostly be the same?

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    I am assuming I will have to do something similar to this to asc/desc my columns as I am using server side processing: http://live.datatables.net/rogeloxo/2/edit

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    Yes with SSP your server script will need to handle retrieving the appropriate data based on the column sort.

    You might want to use order.fixed() instead of column().order() in your group-start event. Your updated example:
    http://live.datatables.net/hulosepa/1/edit

    Moved that example to a SSP Datatable:
    http://live.datatables.net/yarafija/1/edit

    Kevin

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1
    edited August 2018

    DataTables warning: table id=example - Requested unknown parameter 'id' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4:

    ...Says it is a very cryptic warning message. I am looking through and do not spot any silly errors. The table populates but no words. https://databasetable-net.000webhostapp.com/

    $(document).ready(function() {
    var asc = true;
    $('#example').DataTable( {
    "processing": true,
    "serverSide": true,
    "ajax": {
    "url": "server.php",
    "type": "POST"
    },
    "columns": [
    { "data": "id" },
    { "data": "first_name" },
    { "data": "last_name" },
    { "data": "position" },
    { "data": "date" },
    { "data": "updated" },
    ],
    order: [[3, 'asc']],
    orderFixed: [[3, 'asc']],
    columnDefs: [{
    targets: 3,
    visible: false
    }],
    rowGroup: {
    dataSrc: 'position'
    }
    });
    $('#example tbody').on('click', 'tr.group-start', function () {
    asc = !asc;
    //table.column(2).order(asc === true? 'asc' : 'desc').draw();
    table.order.fixed( {
    pre: [ 3, asc === true? 'asc' : 'desc' ]
    }).draw();
    });
    });

    I deleted the target 3 in your example along with a few other things but your example still worked. That was the area I thought the issue might be... I will try brainstorming more of this cryptic message.

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    The thing to do is to follow the troubleshooting steps in the link and look at your JSON response. This is what I get:

    {
        "draw": 0,
        "recordsTotal": 22,
        "recordsFiltered": 22,
        "data": [
            ["1", "Robert", "Mueller", "Lawyer", "2018-04-05", "2018-04-05"],
            ["23", "Robert", "Mueller", "Lawyer", "2018-04-05", "2018-04-05"],
            ......
            ["153", "AFD", "NEW", "STAFF", "2018-08-15", "2018-08-15"]
        ]
    }
    

    Your server is returning an array of arrays but you have defined columns.data which causes Datatables to look for an array of objects. You can read more about this here:
    https://datatables.net/manual/data/#Data-source-types

    Essentially you can remove your columns.data option.

    Kevin

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1
    edited August 2018

    Cool, I managed to get rid of all errors myself (another one came up after I fixed the original). Now the problem ironically is I have no errors! I click and nothing sorts (and no errors!).

    $(document).ready(function() {
    var asc = true;
    $('#example').DataTable( {
    "processing": true,
    "serverSide": true,
    "ajax": {
    "url": "server.php",
    "type": "POST"
    },
    order: [[3, 'asc']],
    orderFixed: [[3, 'asc']],
    columnDefs: [{
    targets: -1,
    defaultContent: '<button type="button">Delete</button>'
    }],
    rowGroup: {
    dataSrc: 'position'
    }
    });
    $('#example tbody').on('click', 'tr.group-start', function () {
    asc = !asc;
    //table.column(2).order(asc === true? 'asc' : 'desc').draw();
    table.order.fixed( {
    pre: [ 3, asc === true? 'asc' : 'desc' ]
    }).draw();
    });
    });

    I am guessing the issue is around the rowGroup, dataSrc, and/or orderfixed. Reading those articles now...

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1
    edited August 2018

    Ok so the issue seems to be this... I need target -1 to put the button in the last column. But I need target 3 so the columns will sort. When you have both obviously datatables gets confused.

            columnDefs: [{
            targets: -1,
         defaultContent: '<button type="button">Delete</button>'
         targets: 3,
         visible: false
           }],
    

    Ive also tried the obvious of removing one of the targets. If you remove targets: -1 you get an error because datatables sees an empty column 6. If you remove target 3, there are no errors but no sorting.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    You have server-side processing enabled (serverSide). Does your server.php script actually implement server-side processing? What is the JSON that it is returning?

    I'm not clear why you would need to add targets: 3 to make a column sortable. Perhaps you can link to a test case showing the issue please?

    Allan

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    Here is my link: https://databasetable-net.000webhostapp.com/

    I was using this example given to me earlier: http://live.datatables.net/yarafija/1/edit

    I think that example is not server-side for sorting if I had to guess. I realized I could delete the targets:3 and the table would still work. Which is why I posted. Yes I am using server.php Json using arrays not the object way.

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited August 2018

    You have:

    rowGroup: {
    dataSrc: 'position'
    }
    

    Since you are using arrays you need to use an integer instead of string. The doc shows it will accept either:
    https://datatables.net/reference/option/rowGroup.dataSrc

    Also in looking at your ajax response closer Allan seems correct in asking if your script supports server side processing. If you look at the ajax request in the browser's network tools you will see something like this:

    draw: 1
    columns[0][data]: 0
    columns[0][name]: 
    columns[0][searchable]: true
    columns[0][orderable]: true
    columns[0][search][value]: 
    columns[0][search][regex]: false
    ......
    order[0][column]: 3
    order[0][dir]: asc
    order[1][column]: 3
    order[1][dir]: asc
    start: 0
    length: 10
    search[value]: 
    search[regex]: false
    

    Its requesting draw 1, starting at row 0 ( start) and expect 10 rows (length).

    Your response is draw 0 (should be 1 I think) with 23 records. The recordsTotal and recordsFiltered look right.

        "draw": 0,
        "recordsTotal": 23,
        "recordsFiltered": 23,
    

    However in the ajax response there are 23 rows. With server side processing enabled it should return just the 10 requested rows. And if you look at your sample page at the bottom it says Showing 1 to 10 of 23 entries. Looking at the table there are actually 23 rows displayed even though you are using the default page length of 10.

    The question is do you need server side processing? How many rows of data do you expect in production?

    More about servier side processing can be found here:
    https://datatables.net/manual/server-side

    Kevin

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    Ok. Here is link: 1. https://databasetable-net.000webhostapp.com/

    I thought so too possible I tried removing the target 3.

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    There are a few problems with your page. It starts with the JS and CSS files you are including.

    You are loading datatables.css twice.

    https://stackpath.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css
    https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css
    https://cdn.datatables.net/v/dt/dt-1.10.16/af-2.2.2/b-1.5.1/r-2.2.1/datatables.min.css
    

    You are loading datatables.js twice. You can click the last link and see that it includes DataTables 1.10.16, AutoFill 2.2.2, Buttons 1.5.1, Responsive 2.2.1. RowGroup is not in the list and not installed.

    https://code.jquery.com/jquery-1.12.4.js
    https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js
    https://cdn.datatables.net/v/dt/dt-1.10.16/af-2.2.2/b-1.5.1/r-2.2.1/datatables.min.js
    

    Use the Download Builder to generate the files you want to use and replace the last two CSS and JS includes with these. You want to make sure and not duplicate the CSS and JS includes.

    Kevin

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1
    edited August 2018

    Here is my sever-side script. Two separate sources have said my draw being nulled to 0 instead of 1 is probably within the script. As far as I can see, my script looks the exact same as the example one (of course something small is probably missing). Here is the code:

    <?php
    $table = 'employees';
    $primaryKey = 'id'; // Table's primary key
    
    $columns = array(
        array( 'db' => 'id', 'dt' => 0 ),
        array( 'db' => 'first_name', 'dt' => 1 ),
        array( 'db' => 'last_name',  'dt' => 2 ),
        array( 'db' => 'position',   'dt' => 3 ),
        array( 'db' => 'date',     'dt' => 4 ),
         array( 'db' => 'updated',     'dt' => 5 ),
    );
    
    $sql_details = array(
        'user' => 'id3741634_username',
        'pass' => 'password',
        'db'   => 'id3741634_database',
        'host' => 'localhost'
    );
    
    require( 'ssp.class.php' );
    
    echo json_encode(
        SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
    );
    ?>
    

    Messaged kthorngren he will hopefully get an answer soon. Thanks everyone.

  • chessGuru64chessGuru64 Posts: 79Questions: 18Answers: 1

    Is this what I am missing? From this link here: https://datatables.net/examples/server_side/row_details.html

    Only concern I would have with this link is some of this code in the beginning is object of arrays (which mine is array of arrays). But I do not trigger 'draw' function, which could be the problem!

        var detailRows = [];
    
        $('#example tbody').on( 'click', 'tr td.details-control', function () {
            var tr = $(this).closest('tr');
            var row = dt.row( tr );
            var idx = $.inArray( tr.attr('id'), detailRows );
    
            if ( row.child.isShown() ) {
                tr.removeClass( 'details' );
                row.child.hide();
    
                // Remove from the 'open' array
                detailRows.splice( idx, 1 );
            }
            else {
                tr.addClass( 'details' );
                row.child( format( row.data() ) ).show();
    
                // Add to the 'open' array
                if ( idx === -1 ) {
                    detailRows.push( tr.attr('id') );
                }
            }
        } );
    
        // On each draw, loop over the `detailRows` array and show any child rows
        dt.on( 'draw', function () {
            $.each( detailRows, function ( i, id ) {
                $('#'+id+' td.details-control').trigger( 'click' );
            } );
        } );
    } );
    
This discussion has been closed.