[SOLVED] Sum values from table where...

[Alexhoward]

Hi guys,

 

i was wondering if anyone could help me.

 

I'm making a page for employees to enter their hours.

 

I have to basics down, but i'm now trying to creat a reports page so they can view their total earnings, and a detailed work record, by selected month.

 

I've got a simlar query in ms access but i'm not sure how to word it in php...

 

basically, to find total earnings from total hours, per day:

 

(in access)

iif ([event date] >= [payrate]![start] AND iif [event date] <= [payrate]![end], [payrate]![rate] * [total hours],0)

 

given that there is a table with the |name|start date|end date|rate|

 

i'm just not sure how to do this in php

 

i'll glady give more detail if this is confusing

 

Thanks in advance!

[jonsjava]

could we see the schema of your DB?

[Alexhoward]

Hi thanks for the reply,

 

i'm not sure what you 're after...

[sasa]

in mysql change '[' and ']' with '`'

and '!' with '.'

[jonsjava]

the db/table layout, so we'll know what your rows/tables are named (and what they store) so we can build an accurate sql query.

[Alexhoward]

ah, ok

 

payrate:

name | start date | end date | rate

 

(holding their wage info)

 

hours:

name | date | location | day | month | Year | start time1 | end time 1 | start time 2 | end time 2 | total hours |

 

(hours info, as we do split shifts, i have the total hours calc down)

 

i've put a start/end date on the payrate, to allow for pay rises, without effect total earnings.

 

does this help at all...?

[Alexhoward]

oh,

 

and to sasa

 

i tried it, and i'm getting errors.

 

as i'm guessing i  also need to use { and else

 

thanks for your suggestion

[revraz]

What error specifically?

 

And post the PHP code you are using.

[Alexhoward]

i've probably just coded it wrong..

 

$d = $_POST['day'];
$m = $_POST['month'];
$y = $_POST['year'];

if($y,"-",$m,"-",$d >= 'dynamitepayrate'.'start' AND if($y,"-",$m,"-",$d <= 'dynamiterate'.'start'
{
echo $hours1 + $hours2 * 'dynamitepayrate'.'wage'
{
else{
echo "0"
}

 

i've already defined $hours1 and $hours2 further up the script. they just work out the total hours of the split time

 

actually...looking at it... i don;t think it makes sense

[revraz]

We assumed you were doing this in a MySQL DB and wanted to write a SQL Query to pull it.  Is that a true statement?

[sasa]

are you talk about query (pull out data from database) or procesing data that puled before

[Alexhoward]

Hi guys.

 

ok.

 

i was going to just pull it all from the tables (mysql)

 

but decided it's probaly better and easier to calcuate the wage with the hours from the event date in the 1st instance and insert that into the hours table.

 

then add all the wages up together for the total earnings of all time

 

is that a fair assumption?

[sasa]

what is tormat of date fields in both tables

 

what is in field `end date` in table`payrate` from current rate

[Alexhoward]

hi,

 

i've set the date fields in mysql to date, so: 2008-12-31

 

the code,

 

$d = $_POST['day'];

$m = $_POST['month'];

$y = $_POST['year'];

 

echo $y,"-",$m,"-",$d

 

will produce 2008-12-31 (if that's what's chosen)

 

for the moment as there is no pay changes the start date is 2008-01-01 and the end date is 2010-01-01

[sasa]

try

<?php
// let $start_period and $end_period is start and and date for payment period
$start_perod = '2008-01-01';
$end_period = '2008-01-31';
$sql = "SELECT name, sum(`end time 1` + `end time 2` - `start time1` - `start time 2`) as hours,
hours * rate as many FROM hours 
LEFT JOIN payrate ON `start date` <= `date` AND `end date` >= `date` 
WHERE `date` >= '$start_perod' and `date` <= '$end_period' GROUP BY `name`";
mysql_connect('localhost','root') or die(mysql_error());
mysql_select_db('name_of _your_db') or die(mysql_error());
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)){
echo $row['name'],' work ',$row['hours'],' h  -->',$row['many'], " €<br />\n";
}
?>

[Alexhoward]

thanks a lot,

 

but man this is confusing....

 

i've jiggled it to match my table, however i'm getting the error "Column 'username' in field list is ambiguous"

[Alexhoward]

I'd really like to do something like this:

 

<?php

$d = $_POST['day'];
$m = $_POST['month'];
$y = $_POST['year'];

$test = mysql_query("select wage from dynamitepayrate where username = '".$mysite_username."' AND start <= '".$y,"-",$m,"-",$d."' AND end >= '".$y,"-",$m,"-",$d."'");

$t = mysql_fetch_assoc($test);

echo $t['wage'];

?>

 

it doesn't work, but sure i'm on the right lines...?

[MadTechie]

i have only read the last post, so i maybe missing something here but the SQL should be SOMETHING like this

 

$start_period = '2008-01-01';
$end_period = '2008-01-31';

$test = mysql_query("select wage from dynamitepayrate where username = '".$mysite_username."' AND `date` BETWEEN $start_period and $end_period");

//or

$sd = $_POST['sday'];
$sm = $_POST['smonth'];
$sy = $_POST['syear'];

$ed = $_POST['eday'];
$em = $_POST['emonth'];
$ey = $_POST['eyear'];

$test = mysql_query("select wage from dynamitepayrate where username = '".$mysite_username."' AND day(`date`) > $sd and month(`date`) > $sm and year(`date`) > $sy and day(`date`) < $ed and month(`date`) < $em and year(`date`) < $ey  ");

[Alexhoward]

Hi techie,

 

the second way looks good,

 

however i only have the variables:

 

$d = $_POST['day'];

$m = $_POST['month'];

$y = $_POST['year'];

 

can still do something with these..?

 

also i only have start and end in the payrate table

 

i'd love to do something like

 

<?php
$d = $_POST['day'];
$m = $_POST['month'];
$y = $_POST['year'];


$test = mysql_query("select wage from dynamitepayrate where username = '".$mysite_username."' 
                             AND $y,'-',$m,'-',$d > start 
                             AND $y,'-',$m,'-',$d < end");
?>

 

 

[MadTechie]

How are the wage and payrate tables linked?

i am guessing its username

 

so try this

<?php
$d = (int)$_POST['day'];
$m = (int)$_POST['month'];
$y = (int)$_POST['year'];
$date = date("Y-m-d",mktime(0, 0, 0, $m, $d, $y));

$test = mysql_query("SELECT wage FROM `dynamitepayrate` LEFT JOIN payrate ON `dynamitepayrate`.`username` = `payrate`.`username` WHERE username = '$mysite_username' AND `payrate`.`start date` <= $date AND `payrate`.`end date` >= $date ");
?>

 

EDIT: oops wrong format for date (now fixed)

[Alexhoward]

hey!!

 

i'm soo effing happy!!

 

thanks to everyone for all your help on this one; got it work like this:

 

<?php
$d = $_POST['day'];
$m = $_POST['month'];
$y = $_POST['year'];


$test = mysql_query("select wage from dynamitepayrate where username = '".$mysite_username."' AND '$y-$m-$d' between start AND end ") ;

$t = mysql_fetch_assoc($test);

echo ($hours1 + $hours2) * $t['wage'] ;
?>

 

where $hours1 is the diffence between the 1st set of times, and $hours2 the second

 

sweet!!

 

the second and last question, for this section then, is how to add up all the rows from the table (hours(now including amount earned)), where the username = $mysite_username

 

firstly for all time,

 

secondly by month

 

Thanks again!!

 

p.s. is between saying >= and <= or just > and < ....?

[MadTechie]

BETWEEN is equivalent to the expression (min <= expr AND expr <= max)

 

EDIT:

Not sure what the second question was ?

you wish to add up the values of the field wage

if so

$t = mysql_fetch_assoc($test);

echo ($hours1 + $hours2) * $t['wage'] ;

to

$total = 0;
while($t = mysql_fetch_assoc($test))
{
$total = $total + (($hours1 + $hours2) * $t['wage']);
}
echo $total;

[Alexhoward]

cheers techie,

 

Aplogies,

 

on their reports page i'd like a cell that shows there earnings of all time.

 

so,

 

Total Earnings : (a sum of every earnings amount from the table attached to their username)

 

by month: (select a month) (a sum of every earnings amount from the table attached to their username and chosen month)