I have create a html table which contain of 2 years data score with same staff_id. How do I get the score value for 2021 using assessment_year_id but same staff_id ?
What I have tried:
$ListPerformanceYear = array();
$mySQL = 'SELECT id, year_assessment FROM assessment_year WHERE type_assessment = "Performance Assessment" AND year_assessment = "'.$filter['year'].'"' ;
$result = mysql_query($mySQL);
if ($result){
if (mysql_num_rows($result) > 0){
while ($row = mysql_fetch_assoc($result)){
$ListPerformanceYear[$row['id']] = $row['year_assessment'];
}
}
}
$ListPerformanceYearBefore = array();
$mySQL = 'SELECT B.id, B.year_assessment FROM assessment_performance A INNER JOIN assessment_year B ON A.assessment_year_id = B.id INNER JOIN system_userprofile C ON C.id = A.staff_id WHERE B.type_assessment = "Performance Assessment" AND A.year_assessment = "'.($filter['year'] - 1).'"' ;
$result = mysql_query($mySQL);
if ($result){
if (mysql_num_rows($result) > 0){
while ($row = mysql_fetch_assoc($result)){
if (!empty($row['year_assessment'])){
$ListPerformanceYearBefore[$row['id']] = $row['year_assessment'];
}
}
}
}
$mySQL = 'SELECT A.id, A.fullname, A.staff_type, A.date_hired, A.department AS departmentID, B.status as staffstatus, C.*, E.description AS department FROM system_userprofile A INNER JOIN system_user B ON A.id = B.id INNER JOIN assessment_performance C ON A.id = C.staff_id INNER JOIN assessment_year D ON D.id = C.assessment_year_id LEFT JOIN system_department E ON E.id = A.department '.$myWHERE.' ORDER BY A.fullname, E.seq';
$result = mysql_query($mySQL);
if ($result){
if (mysql_num_rows($result) > 0){
while ($row = mysql_fetch_assoc($result)){
if (!array_key_exists($row['department'], $ListData)){
$ListData[$row['department']] = array();
$ListData[$row['department']][$row['id']]['assessment'] = array();
if (empty($row['staffstatus'])){
$ListData[$row['department']][$row['id']]['assessment'][$row['assessment_year_id']]['fullname'] = '<span style="color: #f00 !important;">(RESIGNED) </span>' . $row['fullname'];
}
else{
$ListData[$row['department']][$row['id']]['assessment'][$row['assessment_year_id']]['fullname'] = $row['fullname'];
}
$ListData[$row['department']][$row['id']]['assessment'][$row['assessment_year_id']]['final_score'] = $row['final_score'];
}
}
}
}
<table id="table-listing">
<thead>
<tr>
<th>#</th>
<th>Staff Name</th>
<th>Score 2021</th>
<th>Score 2022</th>
</tr>
</thead>
<tbody>
<?php
$i = 0;
foreach($ListData as $key2 => $value2){
foreach($value2 as $key => $value){
$i++;
echo '
<tr>
<th>'.$i.'</th>';
foreach($ListPerformanceYear as $keyX => $valueX) {
if(array_key_exists($keyX, $value['assessment'])){
echo '
<th><a href="15006-assessment-form.php?id='.$key.'" target="_blank">'.$value['assessment'][$keyX]['fullname'].'</a></th>
<th>'.$value['assessment'][$keyX]['final_score'].'</th>
<th>'.$value['assessment'][$keyX]['final_score'].'</th>';
}
}
echo '</tr>';
}
}
?></tbody>
<script type="text/javascript">
$(document).ready(function(){
<pre>$("#table-listing").show();
var table = $('#table-listing').DataTable({
"dom": "<'row'<'col-md-6 col-sm-12 table-listing-button-1'l><'col-md-6 col-sm-12 table-listing-button-1'f>><'row' <'col-md-12 table-listing-button-2'B>r><'table-scrollable't><'row'<'col-md-5 col-sm-12'i><'col-md-7 col-sm-12'p>>",
buttons: [
{
extend: 'colvis',
text: 'Column',
className: 'btn btn-primary'
},
{
extend: 'excelHtml5',
exportOptions: {
columns: ':visible'
},
className: 'btn btn-info'
},
{
extend: 'pdfHtml5',
exportOptions: {
columns: ':visible'
},
className: 'btn btn-info'
}
],
"bStateSave": true,
"lengthMenu": [
[25, 50, 75, -1],
[25, 50, 75, "All"]
],
"pageLength": 25,
"pagingType": "full_numbers",
"columnDefs": [{
'orderable': false,
'targets': [0,2]
},
{
"searchable": false,
'targets': [0,2]
},
{
"visible": false,
"targets": 'hideme'
}],
"drawCallback": function ( settings ) {
var api = this.api();
var rows = api.rows( {page:'current'} ).nodes();
var last=null;
var totalColumn = $('#table-listing').DataTable().columns(':visible').nodes().length;
api.column(1, {page:'current'} ).data().each( function ( group, i ) {
if ( last !== group ) {
$(rows).eq( i ).before(
'<tr class="group bg-grey sbold"><td colspan="'+totalColumn+'">'+group+'</td></tr>'
);
last = group;
}
} );
},
});
});