I have used ssp datatables and inside it i want to show the data from another table in 5th column. here is the part where i have used another query inside formatter. i want to get the answers based on the question id's.
array(
"db" => "Id",
"dt" => 4,
"formatter" => function ($value, $row) {
$dbDetails_1 = array(
"host" => $this->db->hostname,
"user" => $this->db->username,
"pass" => $this->db->password,
"db" => $this->db->database,
);
$table_1 = <<<EOT
(SELECT * FROM `answersmcq` WHERE QuestionId = {$value}) temp
EOT;
$primaryKey_1 = "Id";
$columns_1 = array(
array(
"db" => "Options",
"dt" => 0
),
array(
"db" => "IsCorrect",
"dt" => 1
)
);
$data = \SSP::simple($_GET, $dbDetails_1, $table_1, $primaryKey_1, $columns_1);
if (isset($data['data'])) {
$html = "<ul>";
foreach ($data['data'] as $options) {
if ($options[1] == 1) {
$html = $html . "<li class='text-success'>" . $options[0] . "</li>";
} else {
$html = $html . "<li>" . $options[0] . "</li>";
}
}
$html = $html . "</ul>";
} else {
$html = "";
}
return $html;
}
),
It works fine with first page. it is showing the another table data in 5th column. but when i navigate to other pages. it returns the following json. {"draw":2,"recordsTotal":4,"recordsFiltered":4,"data":[]}
see here i am getting 4 records but the data array is empty. it shows the data only for the first 10 rows in 5th column. rest is working fine. it is causing problem only on sub query execution. same goes with the search option. it only shows the sub data in first 10 rows.