467,080 Members | 1,081 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,080 developers. It's quick & easy.

Calculating total amount per quater

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
  • viewed: 1993
Share:
9 Replies
ronnil
Expert 100+
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
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
Expert 4TB
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
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
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
Expert 4TB
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
[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
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
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by The Bit Bandit | last post: by
10 posts views Thread by Howard Martin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.