Web Design Forum: ma_anse - Viewing Profile - Web Design Forum

Hi guys,

Im having a little trouble here, Im trying to query a DB to gather total hours worked for a month period but split into individual weeks to export to an excel spreadsheet with 4/5 weekly columns.

This is the query i have but im not sure how i should be using the result.

$sql = "SELECT user_id, name
, sum(hours_worked) AS sum_hours
, location, sum(lunch) AS sum_lunch
, sum(break) AS sum_break
, sick, holiday
, clock_status.pay_rate AS pay_rate 
, amend_time BETWEEN '$period1' AS week1
, amend_time BETWEEN '$period2' AS week2
, amend_time BETWEEN '$period3' AS week3
, amend_time BETWEEN '$period4' AS week4
FROM clock_status , staff                
WHERE auth = 1 and clock_status.user_id = staff.staff_number
and clock_status.location = '3301'
GROUP BY user_id, week1, week2, week3, week4, pay_rate ";  

and this is the while loop:

while($d = mysql_fetch_array($query)){
$id = $row-1;
							
$objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $d['user_id']);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $d['name']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$row, '=I'.$row);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $row, $rate);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$row, '=IF(F'.$row.'='.$low.',0.53,0)+IF(F'.$row.'='.$high.',0.64,0)');
$objPHPExcel->getActiveSheet()->setCellValue('H'.$row, '=SUM(E'.$row.'*F'.$row.')+E'.$row.'*G'.$row.'');
$objPHPExcel->getActiveSheet()->setCellValue('I' . $row, $d['sum_hours']);
$objPHPExcel->getActiveSheet()->setCellValue('J' . $row, $d['sum_hours']);
$objPHPExcel->getActiveSheet()->setCellValue('K' . $row, $d['sum_hours']);
$objPHPExcel->getActiveSheet()->setCellValue('L' . $row, $d['sum_hours']);
$row++;
}

Its the weeks 1-4 and the columns i,j,k,l, im stuck on, how do i get the information for the weeks into those columns??