Query result

PHP
1

Hi guys, i have been posting in the SQL section with a few other issues and the result of the issues has led to a problem which i believe is more suited to the PHP forums.

The original thread is here http://www.sitepoint.com/forums/mysql-182/query-sum-765938.html

This is what im having a problem with. I have this query:

$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 im trying to echo the value of sum_hours by week1 / sum_hours by week2 …etc

How do i go about this?

I dont want to post the code as there is a hell of a lot but the result is in a while loop and if i just echo $row[‘sum_hours’] it works perfectly…

however it needs to be something like $row[‘sum_hours’] by $row[‘week1’] for example.

any ideas?

2

i suggest you test your sql first, before working on the php

you’ve got some really bad syntax errors in your FROM clause

3

This query won’t work (it’s the one with the BETWEEN lines in the FROM clause). Why don’t you post the correct one?

if i just echo $row[‘sum_hours’] it works perfectly…

however it needs to be something like $row[‘sum_hours’] by $row[‘week1’] for example.

And doesn’t it work when you echo $row[‘sum_hours’] by $row[‘week1’]?

4

A 1000 apologies, I copied the wrong code, i have now amended the post with the correct code.

as for the $row[‘sum_hours’] by $row[‘week1’] bit that was how i want it to echo but i dont know the syntax to do it.

This is actually a being used in PHPexcel to export the data into excel, the current line of code for that particular cell is:

$objPHPExcel->getActiveSheet()->setCellValue('I' . $row, '='.$d['sum_hours'].'/86400');

but changes to the project requirement means having to split that cell into a weeks data so the whole spreadsheet will have 4/5 columns like that - one for each week.

5

Ok, lets see if i can simplify my requirement as i seem to be getting nowhere fast… i know there is a genius out there with the answer somewhere… :lol:

ok this is the query (changed slightly)


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 >= '$period1s' AND amend_time < '$period1e' AS week1
, amend_time >= '$period2s' AND amend_time < '$period2e' AS week2
, amend_time >= '$period3s' AND amend_time < '$period3e' AS week3
, amend_time >= '$period4s' AND amend_time < '$period4e' 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

this will give me all the hours in the database for the ranges above.

while($d = mysql_fetch_array($query10)){
echo $d['sum_hours'];
}

how do i make it give me sum_hours by week no.

ie.
sum_hours from week1
sum_hours from week2
etc…

Hopefully this makes a bit more sense…