Im after a little help with 2 parts of my project, hopefully its something im just completely missing the point on.
Firstly, Im querying a table in the DB that has people and times basically, when the query is run it uses the sum feature to add up the entries for each person. This works fantastically except due to extra functionality needed ive had to add a new field to the table. So my sum now needs to take this field into account…
So i would want it to add Johns seconds together (id)1+3 but not id 4 as the rate is different…
Secondly, on the same table(there are more fields than the example above) there is a date field. At the moment i query the data for the required date and us PHPexcel to export it, and basically i get the above data (after the sum) written to a spreadsheet in a similar style to above. What i need though is to get that data over a date range (which i have done) but then split it into 7 day periods.
The data needed is from the last Friday of last month to the last Friday of the current month, and split into weeks (Fri-Thurs).
example:
month range (friday to friday)
id------name------seconds------week1-----week2------week3------week4
Apologies if that doesn’t make sense, hopefully it does…
If someone could point me in the correct direction i would be great full.
For the first part you just need to use GROUP BY name, rate to split it out to L/H
As for the second part … I would probably find out the dates of the two needed Fridays (start on day one of the month after the month you’re looking for and go back day by day until you hit a Friday), request everything in between those two, and handle the rest in PHP.
Hm, I just thought of an alternative to step 2, but it’s a bit hacky. Should work though.
Get the date of the last Friday of the previous month
Get the date of the last Friday of this month
Get the date of the intervals.
So for this month it would be from 2011/05/27 until 2011/06/24, with in between 2011/06/03, 2011/06/10 and 2011/05/17.
Okay, s
Week 1 is from 2011/05/27 until 2011/06/03
Week 2 is from 2011/06/04 until 2011/06/10
Week 3 is from 2011/06/11 until 2011/05/17
Week 4 is from 2011/05/18 until 2011/06/24
So now you have 4 ranges you can add in your query:
SELECT
some
, fields
, date BETWEEN '2011-05-27' AND '2011-06-03' AS week1
, date BETWEEN '2011-06-04' AND '2011-06-10' AS week2
, date BETWEEN '2011-06-11' AND '2011-06-17' AS week3
, date BETWEEN '2011-06-18' AND '2011-06-24' AS week4
...
GROUP BY
name, week1, week2, week3, week4, rate
Like I said … hacky
PS. Beware that between the last Friday of last month and last Friday of this month are not necessarily 4 weeks; it may be five!
That seems like a good approach, and to be honest although i didn’t know how to go about it, it looks exactly how i want it. The 4/5 week issue is what was sticking out as a possible problem, not quite sure how to overcome this part.
I know you said its hacky but im not to concerned as this website wont ever be on the public internet. Its an internal thing only.
Is there some kind of php function that can pick out the dates of all the Fridays in a given date range? And can the sql query be built on the result of such a function? Something similar to a num_rows type of thing.
Sorry for the non tech jargon, however i am still learning this PHP sql stuff.
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[0]. " 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…
$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,
[B][COLOR="Red"] 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[/COLOR][/B]
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
;"
What is the part in red supposed to do?
They aren’t tables, so they shouldn’t be in the FROM clause, that’s for sure.
They are to select the data from the amend_time table in weekly intervals and store as an alias.
What im hoping to get from this is to query the data for a monthly period and split into weeks. That data will then be exported to excel so each week will be in a column of its own.
Thanks guys, i knew it would be something obvious, i will be taking guido2004’s formatting advice from now on. My query scrolled right across the screen so completely missed the FROM bit.
Thanks again guys… Now to make all the data export…
Originally this was a weekly export, so using PHPexcel i would have used the sum_hours to put the number of hours into row I on excel. Now this is a monthly thing how do i use the sum_hours and the week1/week2 etc…
