Assign Date Loup in Reports

Hi nmsinc.

What is the PHP error you getting?

It looks like the while loop you have is infinite, I can't see a way it can be exited, I think you might need to use an if statement instead. 

Thanks!

Greg

I have recoded using the where statment (see below) - this returns a PHP syntax error as follows:

MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= strtotime('2013-02-01'); $end = strtotime('2013-02-28'); $date = $start; ' at line

SELECT DATE_FORMAT(date_hours_worked, '%M %D %Y') as 'Work Date',
       assinged_to_which_group           as 'Member Company',
       employee_name                     as 'Imployee Number',
       hours_worked                      as 'Hours Worked',
       payroll_accounting_group          as 'Work Group'
FROM `<?php echo $TABLE_PREFIX ?>paysheet_hours`

$start = strtotime('2013-02-01');
$end = strtotime('2013-02-28');
$date = $start

WHERE assinged_to_which_group = 176 AND date_hours_worked > $date AND date_hours_worked < $end
GROUP BY date_hours_worked
ORDER BY YEAR(date_hours_worked) DESC, MONTH(date_hours_worked) DESC

Hi,

I think the problem is your mixing both PHP and MYSQL code. What are the $start, $end and $date variables used for? Could you give me the entire pages code? 

Thanks!

Greg

Your right on that - I have changed the code as follows, however, the final reports all dates in the file and I only need the dates expressed in the strtotime!

SELECT DATE_FORMAT(date_hours_worked, '%M %D %Y') as 'Work Date',
       assinged_to_which_group           as 'Member Company',
       employee_name                     as 'Imployee Number',
       hours_worked                      as 'Hours Worked',
       payroll_accounting_group          as 'Work Group'
FROM `<?php echo $TABLE_PREFIX ?>paysheet_hours`
<?php
$start = strtotime('2013-02-10');
$end = strtotime('2013-02-28');
$timedate = strtotime('date_hours_worked');
?>

<?php if ($timedate > $start OR $timedate < $end): ?>

WHERE assinged_to_which_group = 176
GROUP BY date_hours_worked
ORDER BY YEAR(date_hours_worked) DESC, MONTH(date_hours_worked) DESC

<?php endif; ?>

Hi,

I think you need to add the dates to your where statement:

SELECT DATE_FORMAT(date_hours_worked, '%M %D %Y') as 'Work Date',
       assinged_to_which_group           as 'Member Company',
       employee_name                     as 'Imployee Number',
       hours_worked                      as 'Hours Worked',
       payroll_accounting_group          as 'Work Group'
FROM `<?php echo $TABLE_PREFIX ?>paysheet_hours`
<?php
$start = date("Y-m-d 00:00:00", strtotime('2013-02-10'));
$end = date("Y-m-d 00:00:00", strtotime('2013-02-28'));
$timedate = strtotime('date_hours_worked');
?>

<?php if ($timedate > $start OR $timedate < $end): ?>

WHERE assinged_to_which_group = 176 AND date_hours_worked > '<?php echo $start; ?>' AND date_hours_worked < '<?php echo $end; ?>'
GROUP BY date_hours_worked
ORDER BY YEAR(date_hours_worked) DESC, MONTH(date_hours_worked) DESC

<?php endif; ?>

The code will return all results between these two dates, so you might need to change it slightly if you want it to return items from just these two dates. 

I'm not sure if date_hours_worked is the correct field to use in the where loop, so you might need to change that as well.

I've also used the date function to convert your strings into the correct date time format for MySQL.

Let me know if you have any questions.

Thanks!

Greg

Thanks Greg - your suggestion worked great!

Hi,

There is no simple way to have a total field at the end of the report. 

The simplest solution would be to have a second report that just returns a row of the total hours worked, but I'm not sure how useful that would actually be.

I can guide you through creating this report if you need.

Thanks!

Greg