Hi There
I need help wrt to my sql query that I am using to display employees timesheet information, as I would like the info to display in one row per employee. At the moment it displays numerous rows for a employee with the different time in and time out times from Sunday to saturday in different rows.
This is the sql:
$ts_timesheet = $wpdb->get_results(“SELECT wp_wpts.ID, wp_wpts.ts_author, date_format(wp_wpts.ts_time_in,’%M %e, %Y’), wp_wpts.ts_job_name, wp_wpts.ts_description, date_format(wp_wpts.ts_time_in, ‘%h:%i %p’), date_format(wp_wpts.ts_time_out, ‘%h:%i %p’), SEC_TO_TIME(TIMESTAMPDIFF(SECOND,wp_wpts.ts_time_in,ts_time_out)),wp_usermeta.meta_value, TIMESTAMPDIFF(SECOND,wp_wpts.ts_time_in,ts_time_out)/3600*wp_usermeta.meta_value FROM wp_wpts, wp_usermeta, wp_users WHERE wp_wpts.ts_time_in >= '”.$ts_d1."’ AND wp_wpts.ts_time_out <= ‘".$ts_d2."’ AND wp_usermeta.user_id = wp_wpts.ts_author AND wp_usermeta.meta_key =‘Bill_Rates’ AND wp_users.ID=wp_wpts.ts_author ORDER BY wp_users.user_login ASC,wp_wpts.ts_time_in", ARRAY_N);
And display in html is:
<?php
for ($i = 0; $i < sizeof($ts_timesheet); $i++) {
if ($i&1) {$alternate = 'alternate';} else {$alternate = '';}
$curauth = get_userdata($ts_timesheet[$i][1]);
?>
|
<?php
//display Employee Name
echo $curauth->user_login?> |
<?php
//display Job Name
echo $ts_timesheet[$i][3]?> |
<?php
//display Job Description
echo $ts_timesheet[$i][4]?>
Add
<?php if (current_user_can('manage_options')) { ?> | Edit<?php } ?>
|
<?php
//display Time in for Saturday
$Day = date('l', strtotime( $ts_timesheet[$i][2]));
if ($Day =="Saturday"){
echo $ts_timesheet[$i][5];
}//end if
?> |
<?php
//display Time out for Saturday
if ($Day =="Saturday"){
echo $ts_timesheet[$i][6];
}//end if
?> |
<?php
if ($Day =="Sunday"){
echo $ts_timesheet[$i][5];
}//end if
?> |
<?php
if ($Day =="Sunday"){
echo $ts_timesheet[$i][6];
}//end if
?> |
<?php
if ($Day =="Monday"){
echo $ts_timesheet[$i][5];
}//end if
?> |
<?php
if ($Day =="Monday"){
echo $ts_timesheet[$i][6];
}//end if
?> |
<?php
if ($Day =="Tuesday"){
echo $ts_timesheet[$i][5];
}//end if
?> |
<?php
if ($Day =="Tuesday"){
echo $ts_timesheet[$i][6];
}//end if
?> |
<?php
if ($Day =="Wednesday"){
echo $ts_timesheet[$i][5];
}//end if
?> |
<?php
if ($Day =="Wednesday"){
echo $ts_timesheet[$i][6];
}//end if
?> |
<?php
if ($Day =="Thursday"){
echo $ts_timesheet[$i][5];
}//end if
?> |
<?php
if ($Day =="Thursday"){
echo $ts_timesheet[$i][6];
}//end if
?> |
<?php
if ($Day =="Friday"){
echo $ts_timesheet[$i][5];
}//end if
?> |
<?php
if ($Day =="Friday"){
echo $ts_timesheet[$i][6];
}//end if
?> |
<?php
//display Total Hours for employee
echo substr($ts_timesheet[$i][7], 0, -3)?> |
<?php
//display Charge rate of employee
echo $ts_timesheet[$i][8]?> |
R <?php
//display Total amount paid
echo $ts_timesheet[$i][9]?> |
<?php
}//end for loop
?>
