Reference PHP JSON Data in Columns.Render directly from data object?

mikepfly2

I have a pretty straightforward table that is fed with object JSON data from PHP using

echo json_encode(array('data' => $arr_deliveries));

I can't figure out how to reference the data directly in the columns.render.

Here is a sample of data:

{"data":[
{"event_id":"44","company_id":"5","description":"test2","trucktype":"Cement","gc_pointperson":"-1","site_description":"Bravo","oversizeload":"0","badgeddriver":"0","numberoftrucks":"","multipletrucks":"0","truckHeight":"","truckLength":"","truckWidth":"","truckweight":"","status":"0","expediteddelivery":"0","date_start":"2018-11-22","time_start":"12:30:00","time_end":"13:00:00","create_date":"2019-11-11 16:24:24","review_comments":null,"review_date":null,"deliveredstamp":"2019-11-15 10:55:59","ponumber":"","dotpermit":"0","title":"","co_name":"GL Plumbing","gc_pointpersonFN":null,"gc_pointpersonLN":null,"original_filename":null,"filename":null,"file_extension":null,"reviewed_byFN":null,"reviewed_byLN":null,"received_byFN":"Michael","received_byLN":"Flynn","requested_byFN":"Jane","requested_byLN":"HVAC","pointpersonFN":"Flynn","requirebadge":"Y","project_id":"1"},
{"event_id":"1","company_id":"3","description":"deliverry","trucktype":"Box Truck","gc_pointperson":"-1","site_description":"Alpha","oversizeload":"0","badgeddriver":"0","numberoftrucks":"","multipletrucks":"0","truckHeight":"","truckLength":"","truckWidth":"","truckweight":"","status":"1","expediteddelivery":"0","date_start":"2019-10-17","time_start":"14:30:00","time_end":"15:00:00","create_date":"2019-10-16 14:03:25","review_comments":"you can do it","review_date":"2019-11-10 07:02:14","deliveredstamp":"2019-11-11 16:05:48","ponumber":"","dotpermit":"0","title":"","co_name":"14Fathoms, LLC","gc_pointpersonFN":null,"gc_pointpersonLN":null,"original_filename":null,"filename":null,"file_extension":null,"reviewed_byFN":"Michael","reviewed_byLN":"Flynn","received_byFN":"Gate","received_byLN":"User","requested_byFN":"Michael","requested_byLN":"Flynn","pointpersonFN":"Flynn","requirebadge":"Y","project_id":"1"}
]}

What I've been doing is making an array of data before the render like "data: {status: 'status', event_id: 'event_id'}", which works but doesn't seem right. I thought I could use data.event_id or data['status'] directly from the primary data from PHP, but neither work.

Also, if I add data:data in the datatable object it gives an "data is not defined error" so I've comment out.

Is this the right way to go about it? Or am I making a simple mistake?

var tblstatus = $('#tbldeliverystatus').DataTable({
        ajax: "./command/homepage_events.php?action=deliverystatus",
        deferRender: true,
        stateSave: true,
       // data: data,
        columns: [
            {
                data: {status: 'status', event_id: 'event_id'},
                render: function (data, type) {
                    if (type === 'display' || type === 'filter') {
                        var statusword = '';
                        var statusclass = '';

                        switch (data['status']) {
                            case '-1':
                                statusword = 'Denied';
                                statusclass = 'important';
                                break;
                            case '0':
                                statusword = 'Pending';
                                statusclass = 'warning';
                                break;
                            case '1':
                                statusword = 'Approved';
                                statusclass = 'success';
                                break;
                        }
                        if (isadmin) {
                            return '<span class="m-4"><input type="checkbox" class="pendingChk" value="' + data['event_id'] + '">&nbsp&nbsp<span class = "lblstatus label label-' + statusclass + '">' + statusword + '</span></span>';
                        } else {
                            return '<td><span id="statusword" class = "label label-' + statusclass + '">' + statusword + '</span></td>';
                        }
                    }
                    return data;
                }
            },
            {
                data: {
                    co_name: 'co_name',
                    id: 'event_id',
                    requested_byFN: 'requested_byFN',
                    requested_byLN: 'requested_byLN'
                },
                render: function (data, type) {
                    if (type === 'display' || type === 'filter') {

                        return '<td>' + data['co_name'] + '<br><span class="muted">' + data['requested_byLN'] + ', ' + data['requested_byFN'].substring(0, 1) + '</span><br>[D' + data['event_id'] + ']</td>';
                    }
                    return data;
                }
            },
            {
                data: 'date_start',
                render: $.fn.dataTable.render.moment('MM/DD/YY ddd') // 10/16/20 Fri
            },
            {data: 'time_start', render: $.fn.dataTable.render.moment('HH:mm:ss', 'h:mm a')},
            {data: 'time_end', render: $.fn.dataTable.render.moment('HH:mm:ss', 'h:mm a')},
            {data: 'description'},
            {data: 'trucktype'},
            {data: 'gc_pointpersonFN'},
            {data: 'site_description'},
            {data: 'oversizeload'},
            {data: 'expediteddelivery'},
            {data: 'badgeddriver'},
            {data: 'create_date'},
            {
                data: 'review_date',
                render: $.fn.dataTable.render.moment('YYYY-MM-DD HH:mm:ss', 'MM/DD/YY')
            },
            {data: 'deliveredstamp', render: $.fn.dataTable.render.moment('YYYY-MM-DD HH:mm:ss', 'MM/DD/YY')},
            {data: null},//action buttons
        ],
        columnDefs: [{
            targets: -1,
            data: null,
            defaultContent: "<button>Click!</button>"
        }],

    });

Debugger code (debug.datatables.net): uyaguh
Error messages shown: none
Description of problem: see above

Thank in advance.

kthorngren

The columns.data won't take multiple objects like data: {status: 'status', event_id: 'event_id'},. You can use data: null, then access all the data of the row using either the data or row parameters.

Kevin

mikepfly2

Thanks for the suggestion. I was able to get it to work adding row to the function. Is this ok to do?

{
     data: 'null',
     name: 'Description',
     className: "dt-left",
     defaultContent: '',
     render: function (data, type, row) {
          if (type === 'display' || type === 'filter') {
            return  row['description'] + ' ' + row['co_name'];
          }
            return data;
          }

Not sure I agree columns.data won't take an array of data. It is in this code and is outputting as I'd expect. Should it not be?

 {
        data: {
              co_name: 'co_name',
              id: 'event_id',
              requested_byFN: 'requested_byFN',
              requested_byLN: 'requested_byLN'
          },
          name: "Company",
          render: function (data, type) {
               if (type === 'display' || type === 'filter') {
                      return data['co_name'] + '<br><span class="muted">' + data['requested_byLN'] + ', ' + data['requested_byFN'].substring(0, 1) + '</span><br>[D' + data['event_id'] + ']';
                }
                    return data;
          }
},

Thanks again.

kthorngren

Not sure I agree columns.data won't take an array of data. It is in this code and is outputting as I'd expect. Should it not be?

Remove the render function and you won't see the output you want. Take a look at this example:
http://live.datatables.net/siziheto/1/edit

It has this:

      { 
        "data": 
        {name: "name", position: "position" },
        render: function (data, type, row) {
          console.log(data);
          return data;
        }
      },

The output in the table is [object Object]. The output in the console is the full data set for the row not just the name and position objects. This is the same behavior as using data: null.

Using the row parameter is good for accessing the full row of data.

Kevin