how to calculate an amount using sql query command

[nabilamn]

hai all.
i got a problem to display a total from a few data in my database
for example, in my database i got these fields:

TABLE STUFF

food double;
clothes double;
groceries double;
total double; ---> should i put this in my table to save my total that will be calculated later ?

got it ? then, using a form in my web, i will insert the price for these items. then, in the total textfield, i want it to automatically display the grand total of stuff that i buy.
what is the query ? how to save it into my table ? and how to automatically display it ?

what i mean by automatically display the grand total is when i insert price into food the grand total will display the current amount.
TQ !

1. create a form with four fields what you need
2. get the value of the textbox while submit using the javascript and made the calculation for total.
3. Establish the database connection, use the insert query to update the values into the database
4. again Establish or run the query to display the records.

[nabilamn]

can you give me simple example?
i already create the form for these fields complete with the textbox and save button.

[silviuks]

maybe i don't understand your problem, but ...
if you know the values for the other 3 columns i think you don't need to store the total. you can do something like this: select (food + clothes + groceries) as total from table.

[nabilamn]

im working on with the js calculator.

to silviuks, i want the total price. not the total stuff i bought. :)

[silviuks]

TABLE STUFF

food double;
clothes double;
groceries double;
total double; ---> should i put this in my table to save my total that will be calculated later ?

from your table structure, i understand that columns: food, clothes and groceries holds the price for the corresponding items. Is it correct? The query from my previous post gives you the total price for a row.
Also, if you want the total (for entire table) you can use something like this:
SELECT (SUM(food) + SUM(clothes) + SUM(groceries)) as total from table ...

[storm123]

im working on with the js calculator.

to silviuks, i want the total price. not the total stuff i bought. :)

Do you want to do something like this :

<script type="text/javascript">
function totalSum() {
  var food       = document.getElementById('food').value;
  var clothes    = document.getElementById('clothes').value;
  var groceries  = document.getElementById('groceries').value;
  document.getElementById('total').value = Number(food) + Number(clothes) + Number(groceries);
}
</script>
<form name="myform" method="POST" action=""  id="myform">
food :      <input type="text" name="food"      id="food"       value="" onchange="totalSum();"><br>
clothes :   <input type="text" name="clothes"   id="clothes"    value="" onchange="totalSum();"><br>
groceries : <input type="text" name="groceries" id="groceries"  value="" onchange="totalSum();"><br>
total :     <input type="text" name="total"     id="total"      value="" onchange="totalSum();" disabled><br>
<input type="submit" name="buy" value="buy">
</form>

[scaiferw]

Never, ever store information in a database that can be easily calculated from other data in the database. Any time you store the same information in two places, which in a sense is what you're doing here, you introduce the possibility of an error, and unnecessarily complicate your database.

What you want to do is calculate the total in your query and assign the value to an alias, as in;

SELECT food,clothes,groceries,(food+clothes+groceries) AS total FROM table

The alias 'total' can be treated in your PHP like any other value returned by the query.

It's much more efficient from a processing point of view to do the calculation on the database server, and it makes for PHP code that is much easier to understand.

Second that scaiferw.

However, the picture is slightly muddied when you introduce volatile pricing with discounts or manager's special offer. But it seems that your example is far simpler than that. NO stored totals. MYSQL has methods of aggregating all sorts of stuff as well.

[nabilamn]

no discounts and so on. this is just a simple example :)
TQ to you guys ! im little bit confused to use the alias like scaifrew suggest.
however, i tried it !

[scaiferw]

The alias is very handy. In one application, I used it to pull the same date field in two different formats using aliases. It's usually much, much easier to do it that way then converting it in PHP.

You can even use quotes and multi-word aliases, or us it to capitalize field names for display, as in:

SELECT 
  food as Food,
  clothes as Clothing,
  groceries as "Misc. Groceries",
  (food+clothes+groceries) as "Total Expenditure" 
FROM table

If you want to use spaces in the alias, it must be quoted.

You said you tried it, did it work for you?

[nabilamn]

silviuks, i tried yours. and it work ! cool
but the next problem is, i want it to be display when i click another textfield so the total field will display the current value.

[nabilamn]

guys, help me.
i want to calculate the grand total from a group of total. how to calculate ? guide me please.
here is my code:

SELECT food, fruits, clothes, vegetables,(food + fruits + clothes + vegetables) AS total
FROM stuff

[scaiferw]

As far as I can see that query should work, as long as you don't already have a field called 'total' in the table. If it doesn't work, try ... AS thetotal FROM stuff and see what happens.

[nabilamn]

scaifrew, what i want to calculate is the grand total of the totals
after i got the total, i must create another page that will display the grand total. what i want to know is, how i can get all the totals because as far as i know, the AS total (like the previous post) is a temporary field rite.
or should i wrote

...total AS grand total FROM stuff

[scaiferw]

Without having a clearer picture of the larger context, it's hard to say, but remember,if you want to have spaces in your alias, like ...total AS grand total FROM stuff , it must be quoted like ...total AS "grand total" FROM stuff .

To have a grand total on another page, you may need to store the values in a session variable.