Run MySQL query in DataTables server-side processing script

Question

I am using DataTables server-side processing to pull data into a DataTables table from a MySQL table.

This is the working MySQL query I would like to run and display in my DataTables table:

$sql = "SELECT Client,EstimateNumber,Status,TotalEstimatedTime,CostToDateRoleTotal,ROUND((CostToDateRoleTotal/TotalEstimatedTime)*100) as PercentComplete FROM Estimates WHERE " . ($studioName != null ? "Studio = '" . $studioName. "' AND" : '') . " Status != 'Invoiced' AND Status != 'Cancelled' AND TotalEstimatedTime > 0 AND CostToDateRoleTotal > 0 ORDER BY PercentComplete DESC";

I have adjusted the DataTables server-side processing script to be:

<?php

// connection configuration
require_once 'config.php';

// db table to use
$table = 'Estimates';

// table's primary key
$primaryKey = 'EstimateNumber';

$percent_complete = "('CostToDateRoleTotal'/'TotalEstimatedTime')*100";

// 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' => 'TotalEstimatedTime', 'dt' => 3),
array('db' => 'CostToDateRoleTotal', 'dt' => 4),
array('db' => $percent_complete, 'dt' => 4),
); // end columns array

// sql server connection information
$sql_details = array(
'user' => $currentConfig['user'],
'pass' => $currentConfig['pass'],
'db' => $currentConfig['name'],
'host' => $currentConfig['host'],
);

// DataTables helper class
require 'ssp.class.php';

function utf8ize($d) {
if (is_array($d)) {
    foreach ($d as $k => $v) {
        $d[$k] = utf8ize($v);
    }
} else if (is_string ($d)) {
    return utf8_encode($d);
}
return $d;
}


$data = SSP::complex($_GET, $sql_details, $table, $primaryKey, $columns, null, "Status != 'Invoiced' AND Status != 'Cancelled' AND TotalEstimatedTime > 0 AND CostToDateRoleTotal > 0");

echo json_encode(utf8ize($data));

This line is throwing an error:

$percent_complete = "('CostToDateRoleTotal'/'TotalEstimatedTime')*100";

The error is: {"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column '('CostToDateRoleTotal'/'TotalEstimatedTime')*100' in 'field list'"}

In my original $sql query above, I ran this calculation and displayed the outcome as a new column, $percent_complete. I am trying to display those same results in my DataTables table. How can I alter my server-side processing script to perform this calculation and display it in a new column?

Just remove the ' from your percent_complete. check the answer provided below by mike. — Zeff Tang
– Zeff Tang, Commented Sep 29, 2016 at 6:34

Gyrocode.com · Accepted Answer · 2016-09-29 19:43:01Z

2

CAUSE

Class SSP defined in ssp.class.php cannot handle column aliases, expressions or JOINs.

SOLUTION

You need to alter ssp.class.php and remove all ` (backtick) characters that escape column and table names. You would be responsible for escaping column/table names yourself if the name is a reserved word.

Then replace

array('db' => $percent_complete, 'dt' => 4)

with

array('db' => 'ROUND((CostToDateRoleTotal/TotalEstimatedTime)*100)', 'dt' => 4)

edited Sep 29, 2016 at 19:43

answered Sep 29, 2016 at 11:19

Gyrocode.com

59k16 gold badges157 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Liz Over a year ago

Thank you for your reply. If I remove the back ticks from ssp.class.php, would I need to alter my server-side processing script to have back ticks surround my column/table names?

Gyrocode.com Over a year ago

@LizBanach, only if you use names that are reserved keywords, but the names you use (Client, EstimateNumber, etc.) should be OK.

Mike · Accepted Answer · 2016-09-29 06:29:26Z

-1

I don't know how the SSP class formats the query but you might want to try to add "AS percent_complete".

$percent_complete = "(CostToDateRoleTotal/TotalEstimatedTime)*100 AS percent_complete";

When you SELECT a column it needs a name.

answered Sep 29, 2016 at 6:29

Mike

12 bronze badges

1 Comment

Gyrocode.com Over a year ago

Unfortunately the problem is not in the query itself. Class SSP cannot handle column aliases, expressions or JOINs.

Collectives™ on Stack Overflow

Run MySQL query in DataTables server-side processing script

2 Answers 2

CAUSE

SOLUTION

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

CAUSE

SOLUTION

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related