Ive managed to get somewhere with the PHP side of things and created a script that will give me the correct dates for the corresponding month.
I am however having a slight issue with the sql query suggested above.
This is the code i have:
$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 FROM clock_status , staff,
amend_time BETWEEN '$period1' AS week1
, amend_time BETWEEN '$period2' AS week2
, amend_time BETWEEN '$period3' AS week3
, amend_time BETWEEN '$period4' AS week4
, amend_time BETWEEN '$period5' AS week5
WHERE auth = 1 and clock_status.user_id = staff.staff_number
and clock_status.location = '$ident'
GROUP BY user_id, week1, week2, week3, week4, pay_rate "
The $periodX echos as: 2011-06-24 AND 2011-06-30 etc....
$period1 = $date_array. " AND " .date('Y-m-d' , $newdate);
and this is the error i get:
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 'BETWEEN '2011-05-27' AND '2011-06-02' AS week1 , amend_time BETWEEN '2011-06' at line 2
I know its going to be something simple but i just cant see it. Any help would be greatly appreciated....