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
9 2457 ronnil 134
Recognized Expert New Member
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_s tart AND date_column < date_interval_e nd 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.
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.
pbmods 5,821
Recognized Expert Expert
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.
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.
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.
pbmods 5,821
Recognized Expert Expert
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.
[PHP]
session_start() ;
$connection = mysql_connect(" localhost", "root", "issasql");
$select_db = mysql_select_db ("wca", $connection);
mysql_query("SE LECT dateinjury,amou nt FROM invoice WHERE persal = '".$_COOKIE["Persal"]."'")or die(mysql_error ());
//var = $total;
$dateinjury = strtotime ($_POST['date1']);
$dateinjury = strtotime ($_POST['date2']);
echo strtotime($_POS T['date1']), "\n";
echo strtotime($_POS T['date2']), "\n";
/*
while(strtotime ($_POST['date1']) < strtotime ($_POST['date2']))
{
if (strtotime($_PO ST['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
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
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."<b r>";
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: |
last post by:
I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount, Gift_Per_Year,
and Matching_Gift_Ratio.
The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
A total Pledge for all donors would just sum up the calculated values.
|
by: The Bit Bandit |
last post by:
Hopefully someone can help me create a query that I'm having some
trouble with.
I have three tables: invoices, invoicedetails, invoicepayments
The fields are:
invoices
--------
InvoiceNo
|
by: Howard Martin |
last post by:
I have a form at http://www.develop.check.com.au/hh/order.html where
an extended sub-total and then a grand total is calculated as soon as
a quantity is input by a user. I would like to be able to add the
shipping cost to that grand total.
Shipping costs are in the "Postage & handling charges" link and are a
cost per box. If somebody selects "USA, Canada, Middle East" from the
drop down box it should recalculate the shipping cost,...
|
by: MT |
last post by:
Hi,
I have an invoicing system using postgresql. Each time a customer makes a purchase, a new record is inserted into the "cart" table. The "cart" table, among other things, contains a customer_id field and a total for the amount purchased on the particular invoice. Now what I'd like to do is to calculate the total invoices for each customer in a given time interval, then list those customers in descending order from most paying to least...
|
by: steve |
last post by:
Hi All
Not sure if this is the right forum, but I need the formula for calculating
the amount of Sales tax (GST) from the tax included price
In Australia GST is 10% and the standard formula is to divide the total by
11 to get the gst amount
This is great until the GST % changes one day
| |
by: fatimahtaher |
last post by:
Hi,
I am new to C# programming and my first assignment requires me to calculate total interest paid on a loan. The user will input the loan, the interest rate per month, and the monthly payment. It has to use a while loop and calculate the following:
Total amount of interest paid and the number of months to pay off the loan.
I am not a finance major so I have some difficulty with the logistics of how to go about doing this. My code so...
|
by: rcollins |
last post by:
I ahve a database that I put together to keep track of the office supplies. We input what has been purchased and what goes out. When I run a report, usually it is just for a month, but I have a calculation issue with this. If I run the report for the whale time we have used this database, the units on hand is correct. When I run it for specific dates, it only calculates the on hand for these times, throwing off the total of on hand supplies. I...
|
by: charmeda103 |
last post by:
i have to calculate a total amount of pounds for my program. i am getting each pound calculated but i have to add up all those pounds into one total.
the problem is one of the total pounds is not supposed to be calculated.
how do i do this I am struggling
OUTPUT:
Total weight21.5
Total weight30
Total weight50.8
Total weight12.4
|
by: sumit kale |
last post by:
Hi,
Can somebody help me resolve my problem ?
I am getting error when calculating total using unbound textfiled in subform.
I have a main form called purchase_register_master and a subform called purchase_register_details, In sub form i have a unbound textfield called txt_Total_Amount where in i am calculating total amount for different items purchased by giving following code in control source.
ContolSource: =Sum()
In my main form i...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |