Click here to Skip to main content
15,868,030 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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:

PHP
$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>


JavaScript
<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>>", // horizontal scrollable datatable
			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'
				}
			],/*
			rowsGroup: [
				'first:name',
				'second:name'
			],*/
			// Uncomment below line("dom" parameter) to fix the dropdown overflow issue in the datatable cells. The default datatable layout
			// setup uses scrollable div(table-scrollable) with overflow:auto to enable vertical scroll(see: assets/global/plugins/datatables/plugins/bootstrap/dataTables.bootstrap.js).
			// So when dropdowns used the scrollable div should be removed.
			//"dom": "<'row'<'col-md-6 col-sm-12'l><'col-md-6 col-sm-12'f>r>t<'row'<'col-md-5 col-sm-12'i><'col-md-7 col-sm-12'p>>",

			"bStateSave": true, // save datatable state(pagination, sort, etc) in cookie.
			"lengthMenu": [
				[25, 50, 75, -1],
				[25, 50, 75, "All"] // change per page values here
			],
			// set the initial value
			"pageLength": 25,
			"pagingType": "full_numbers",
			"columnDefs": [{  // set default column settings
				'orderable': false,
				'targets': [0,2]
			},
			{
				"searchable": false,
				'targets': [0,2]
			},
			{
				"visible": false,
				"targets": 'hideme' //[1, -1]
			}],
			"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;
					}
				} );
				//console.log('Draw');
			},
			
		});

});
Posted
Updated 14-Aug-22 20:30pm
v4
Comments
Thava Rajan 17-Aug-22 21:03pm    
Try to use group by and format the results

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900