Calculating total amount per quater

mankolele

Hi all
I need an idea on where to start when calculating a total amount betwee n two dates from a database like I want amount of money paid out from 30/03/2004 and 30/07/2004 ,but in the database these dates are not captured as they come they are mixed as some may come two years later to be captured. I have to go throught the database taking in those apropriate dates I need and add them up.

I belive I need to use a while loop and add the one I need and skip those I do not want and take the total at the end.

Thanks

Jun 7 '07 #1

ronnil

if you only want to retrieve rows in a certain interval of values i think all database engines have that compiled in their language

in MySQL you could use "SELECT * FROM table WHERE date_column > date_interval_start AND date_column < date_interval_end ORDER BY date_column ASC"

for more info about date functions in mysql visit:http://dev.mysql.com/doc/refman/4.1/...functions.html
for more info about the SELECT statement in mysql visit http://dev.mysql.com/doc/refman/4.1/en/select.html

this way your rows are also sorted by the date the transaction took place.

Jun 7 '07 #2

mankolele

Thanks for the reply but now I need to use PHP and mysql to get the sum of the amounts on a particular quater, using the loop.

Jun 7 '07 #3

pbmods

Thanks for the reply but now I need to use PHP and mysql to get the sum of the amounts on a particular quater, using the loop.

You can limit your results using the WHERE clause in your MySQL query, which is perfectly accessible in PHP.

If you absolutely have to work with every single (unsorted) record in the database at once, and there's absolutely nothing you can do about it, you'll have to use strtotime to convert the dates into Unix timestamps.

Jun 7 '07 #4

mankolele

Thanks
But I was just told that I can use a while loop to resolve this having if loops inside it to take what I need and skip those I do not need, I've never worked with a while loop I do not know where to start wi th that.

Jun 8 '07 #5

mankolele

You can limit your results using the WHERE clause in your MySQL query, which is perfectly accessible in PHP.

If you absolutely have to work with every single (unsorted) record in the database at once, and there's absolutely nothing you can do about it, you'll have to use strtotime to convert the dates into Unix timestamps.

I have been trying to convert my date but I am comming up with more than what I have in my database now I am not sure what date is it returning and more.I dont clearly catch this strtotime can anyone pliz explian breifly how it works.I am trying to read about it also.

Jun 8 '07 #6

pbmods

I have been trying to convert my date but I am comming up with more than what I have in my database now I am not sure what date is it returning and more.

Let's have a look at the loop you're using now.

dont clearly catch this strtotime can anyone pliz explian breifly how it works.I am trying to read about it also.

strtotime takes in any string that describes a date. It can be a MySQL datetime ('2007-05-30 23:59:59'), full text date ('May 30, 2007 11:59:59 PM'), or even a fuzzy-text date ('Yesterday at 3 PM'). It returns a Unix timestamp that you can use to compare dates.

Jun 8 '07 #7

mankolele

[PHP]
session_start();

$connection = mysql_connect("localhost", "root", "issasql");
$select_db = mysql_select_db("wca", $connection);

mysql_query("SELECT dateinjury,amount FROM invoice WHERE persal = '".$_COOKIE["Persal"]."'")or die(mysql_error());

//var = $total;
$dateinjury = strtotime ($_POST['date1']);

$dateinjury = strtotime ($_POST['date2']);

echo strtotime($_POST['date1']), "\n";

echo strtotime($_POST['date2']), "\n";
/*
while(strtotime ($_POST['date1']) < strtotime ($_POST['date2']))
{
if (strtotime($_POST['date1']) < strtotime ($_POST['date2']))
{

$query = ("SELECT sum(amount) FROM invoice WHERE dateinjury = '".$_POST['date2']."'")or die(mysql_error());
echo "Total Amount" .$query;
}
}*/
[/PHP]

Ok this is totally wrong but is what I am using to try to see what is comming up, even when empty there still is someting comming up.The while loop part is where am trying to get all dates between the two dates, and add up the amounts, anybody with a right way may help.
Thanx

Jun 11 '07 #8

mankolele

Hi all
I do manage to get the sum of all the amounts and display it the amounts paid and out standing amount. Any bright idea I can use to only add amounts that are between two inputed dates. My date type in the database is DATE.

Thanks for any help offerd

Jun 14 '07 #9

mankolele

Hi all

I changed my date to varchar in the database
[PHP]
$m1 = $_POST['m1'];
$d1 =$_POST['d1'];
$y1 = $_POST['y1'];
$date1 = $y1.$m1.$d1 ;
echo $date1."<br>";
$m2 = $_POST['m2'];
$d2 =$_POST['d2'];
$y2 = $_POST['y2'];

$date2 = $y2.$m2.$d2."<br>";
echo $date2;
if( $date2 < $date1 && $date2 > $date1)
{
//do the calculation

}
else if ($dateinjury > $date2)
{
echo "No";
}
[/PHP]

But it is still adding all the dates, I am not sure how to handle the dates between must be the only dates added.At some level it does work but messes up agin.

PLEASE HELP THIS TIME

Jun 19 '07 #10