How to run a function within the $columns array

erbanach

I need to update the $columns array to run PHP functions on the data in this array. This is what my $columns array looks like right now:

$columns = array(
    array( 'db' => 'Client', 'dt' => 0 ),
    array( 'db' => 'EstimateNumber',  'dt' => 1 ),
    array( 'db' => 'Status',   'dt' => 2 ),
    array( 'db' => 'CurrentEstimateTotal',     'dt' => 3 ),
    array( 'db' => 'CreatedDate',     'dt' => 4 )
);

I need to update the CurrentEstimateTotal to add the money_format() PHP function. I also need to use the date_diff() PHP function on the CreatedDate column. Please let me know how to add this functionality in. Thank you!

allan

The demo SSP class has a formatter option in addition to the db and dt options in the column array definitions. This is basically a function (that you define) which will be called with each value for that column and format it as required.

There are three examples of that in this example. The id column adds a prefix string, the start_date column uses a date formatter and the salary column uses a number formatter.

It is important to note that the SSP class is designed for the 80% use case only. It is primarily a demo and doesn't allow complex interactions such as joins, grouping and SQL functions. However, if you don't need them, it can be super useful for setting up a quick server-side processing table!

Regards,
Allan

erbanach

Thank you. I have edited my columns array to the following (I have only edited the CreatedDate column):

// date variables
$current_date = date();
$date = 'CreatedDate';

// array of database columns which should be read and sent back to DataTables.
// the 'db' parameter represents the column name in the database, while the 'dt'
// parameter represents the DataTables column identifier.
$columns = array(
    array( 'db' => 'Client', 'dt' => 0 ),
    array( 'db' => 'EstimateNumber', 'dt' => 1 ),
    array( 'db' => 'Status', 'dt' => 2 ),
    array( 'db' => 'CurrentEstimateTotal', 'dt' => 3 ),
    array(
      'db' => $date,
      'dt' => 4,
      'formatter' => function ($date, $row) {
           print date_diff($date, $current_date);
         }
    )
);

It is outputting nothing. No errors. Is there something wrong with my implementation? Thanks for your help!

allan

If you have a look at your server's error logs I suspect you will find an error related to $current_date since it is being used in a function that doesn't directly have access to that variable. You need to use:

function ($date, $row) use ($current_date) {

The PHP documentation has full details about this if you are interested in more information.

Regards,
Allan

erbanach

Hi Allan! Please forgive me for this long-winded post.

I have run some tests, and have a function working as intended outside of the DataTables $columns array. My goal is to echo a count of the number of days in between today's date and the CreatedDate column in mySQL database in my DataTables column.

This function works as intended, and outputs an echo of the number of days between $current_date and $date as: "16 days live", "15 days live", etc.

$sql = "SELECT CreatedDate FROM Estimates";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
      $created_date = $row["CreatedDate"];
      $date = new DateTime($created_date);
      $current_date = new DateTime();
      $diff = date_diff($date, $current_date);
      echo $diff->format('%d days live');
  }
  } else {
  echo "No results";
}

In my DataTables $columns array, I am trying to achieve the same effect:

// date variable
$created_date = 'CreatedDate';
$current_date = new DateTime();

// array of database columns which should be read and sent back to DataTables.
// the 'db' parameter represents the column name in the database, while the 'dt'
// parameter represents the DataTables column identifier.
$columns = array(
    array( 'db' => 'Client', 'dt' => 0 ),
    array( 'db' => 'EstimateNumber', 'dt' => 1 ),
    array( 'db' => 'Status', 'dt' => 2 ),
    array( 'db' => 'CurrentEstimateTotal', 'dt' => 3 ),
    array(
      'db' => $created_date,
      'dt' => 4,
      'formatter' =>
            function ($created_date, $row) use ($current_date) {
            $date = new DateTime($created_date);
            $diff = date_diff($date, $current_date);
            echo $diff->format('%d days live');
         }
    )
);

By and large, these functions are very similar. But I keep receiving an error of:

PHP Notice:  Trying to get property of non-object.

Which I believe is in reference to either the $created_date or $date variables.

Why am I receiving this error, and how can I fix this? I am having a hard time getting a var_dump() of the $created_date and $date variables within the DataTables columns to see what is the problem, which lead me to do some testing outside of the DataTables $columns array. Am I missing something? Thank you in advance for your help!

erbanach

I got this working! I simply needed to return the result instead of echo. Thanks for your help, Allan!

allan

Two issues I can see:

1: echo $diff->format('%d days live');

Don't echo it. Return it. Let the SSP class do the echoing.

2: $created_date is being used inside the closure function. Just like $current_date it needs to be defined in the use statement, per the PHP documentation. I'm slightly surprised the error is ambiguous about that = what is the line number the PHP error gives?

Allan

erbanach

After I included the return instead of echo, it started working!