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

chessGuru64 · April 2018

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

chessGuru64 · April 2018

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!

allan · April 2018

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

Allan

chessGuru64 · 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.

allan · April 2018

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

chessGuru64 · August 2018

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>'
        }
    ]
} );

} );

chessGuru64 · August 2018

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 }
]
} );

chessGuru64 · August 2018

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>"}
]

kthorngren · August 2018

Sounds like you are asking whether to define your buttons using columns or columnDefs. The answer is "Yes" 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

chessGuru64 · August 2018

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!

chessGuru64 · August 2018

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

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

Thanks. I may have more questions later...

kthorngren · August 2018

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

chessGuru64 · August 2018

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

kthorngren · August 2018

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

chessGuru64 · August 2018

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>

chessGuru64 · August 2018

...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>

kthorngren · August 2018

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

chessGuru64 · August 2018

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?

chessGuru64 · August 2018

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

kthorngren · August 2018

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

chessGuru64 · 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.

kthorngren · August 2018

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

chessGuru64 · 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...

chessGuru64 · 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.

allan · August 2018

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

chessGuru64 · August 2018

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.

kthorngren · 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

chessGuru64 · August 2018

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

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

kthorngren · August 2018

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

chessGuru64 · 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.

chessGuru64 · August 2018

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' );
        } );
    } );
} );

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

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

Answers

Howdy, Stranger!

Categories

DataTables

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

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

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables