code for calc element

[gapper]

Hi,
I have 3 fields (number format) in 3 different tables. I would know the sum into another table field through the calc element. So I wrote the following code:

<?php $query=mysql_query("SELECT quantity FROM tabella1 WHERE =$_GET['62']");("SELECT quantity FROM tabella2 WHERE =$_GET['63']");("SELECT quantity FROM tabella3 WHERE =$_GET['64']");
 
$array=mysql_fetch_array($query);
$quantity_tabella1=$array;$quantity_tabella2=$array;$quantity_tabella3=$array;
 
$somma=$quantity_tabella1+$quantity_tabella2+$quantity_tabella3;
echo $somma;?>

It is not working. Where's the mistake?

 

[gapper]

I have written 2 different codes and both of them don't work.
1) return(int)'{tabella1___quantity}'+'{tabella2___quantity}+'{tabella3___quantity}'
2)
{tabella1___quantity}+{tabella2___quantity}+{tabella3___quantity}

Please, what is the correct code?

 

[troester]

First of all:
inside php code fields (calc element, php plugins...) don't use php tags.

Placeholders {table___element} can only reference fields from your form (so tabella1/2/3 must be somehow joined)
In this case you would do
return (int)'{tabella1___quantity}'+(int)'{tabella2___quantity}'+(int)'{tabella3___quantity}';

Otherwise you have to do it with MySQL calls, not sure what you need; but you should use the Joomla API calls.
(WIKI is still down for "Common php tasks")

You can always include
var_dump($your-vaiable);exit; (which will show you a blank screen with debug info)
to see if your code is run at all and what you get.

 

[gapper]

Thanks for immediate answer. I have done 2 database join elements to the form containing the sum field, but it doesn't do what I would want (the sum) because it shows 0.
That I would like writing can be compared to a Riepilogo sheet of a current account. My idea was to let write numbers in some fields (into different tables) and to sum them into an element which "gather" (sums) the numbers in order to know their sum.
I don't know MySQL and API calls, so I think I haven't the ability to do this part about the sum elements and I don't know where to learn that.

Thanks again for your immediate answers.

 

[gapper]

I have used the calc element into the form with the elements to sum and it worked ok. I I want to see the sum into another table, what ways to link it?
I tried using the database join for the elements to sum and settings: value: id and label: myelementstosum. That doesn't work. So, how can I link the elements coming from a (different) table in the table I would use? At the moment the only ways I can think to solve it are:
1) to use the calc element in the same table with its element to sum; hidden it; copy the list (with calc element); hidden every elements except the calc element
2) to use the calc element in the same table with its element to sum; hidden it; maybe through the autofill element I could see the sum into the list I want.

I have noticed that when I try to add records in the form with calc element, the form is blank, so I think I need however to copy the list with calc element and delete it from the list where I want users adding data.

 

[gapper]

You have written that tabella 1/2/3 must be somehow joined.
What can the possible ways be to join tabella 1/2/3 in order to use the calc element?

 

[cheesegrits]

Do your other three tables have a foreign key, that points to the primary key of your main table?

Or, to put it another way, how are those other three tables related to the one you are displaying in the form?

-- hugh

 

[gapper]

I tried a simpler example:
- list (table): tabella1; element: quantity1
- list (table): sum; element: sum (calc element with the following code: return(int)'{sum___quantity1}'; ); element: quantity1 (databasejoin which points to table sum; value:id; label: quantity1).

In that way I get a dropdown menu which points to quantity1 in tabella1 and sum 0.

About the foreign key, the only element where I have seen it is into cascading dropdown, so I think I haven't foreign key.
I am sure I am making a mistake, but I don't know where.

 

[gapper]

Please, how can I create a foreign key that points to the primary key of my main table? What element to use?

 

[troester]

I still don't know what you want to achieve in the end.

A databasejoin element is creating a foreign key.
So if your list/table sum contains dbjoin elements quant1 (pointing to tabella1 with value=id, label=quantity1), quant2 (pointing to tabella2) you can do
$sum = (int)'{sum___quant1}' + (int)'{sum___quant2}';
return $sum;

With calc element set to "Ajax calulation"=yes it will show the calculated sum after a dropdown value is selected, with "Ajax calculation"=no, the value is calculated on form submit.

 

[gapper]

Thanks. What I would want to achieve is like a basket in a list. This values basket increase or decrease depending on what forms are submitted and should be never smaller than 0.
It should work just like a bank balance.
It is not important for me to see this values basket in the form, but in the list (or better in a page which displays it).
What is the best way to achieve it?

 

[rob]

so its a total of an element value for all records found in the list?
Like the others I'm still not sure what you actually need. Perhaps make a diagram / drawing / video of what you need? Often images are easier to understand than words.

 

[gapper]

Thanks for the answer.
It's a total of elements value found in different lists (tables).
Each addend and each sum is in different table.
E.g.:
Addend 1 in table 1
Addend 2 in table 2
Sum 1 in table 3
Addend 3 in table 4
Addend 4 in table 5
Sum 2 in table 6

Sum 1 = addend 1 + addend 2
Sum 2 = addend 3 + addend 4

- Addends are added by users and sums should be automatic.
- Each addend is a field element (value) and each sum should be a calc element.
- Each sum should be shown only in 1 list (not form).
- I would want to know sum 1 and sum 2.

Sorry I have just tried explaining with another example without images. Please let me know if it is clearer

 

[rob]

still not 100% clear.......

WHERE =$_GET['62']

This makes no sense, and is not correct SQL. First of all i don't understand $_GET['62'] - what are you trying to do here? Secondly the where statement needs a field, e.g.

WHERE foo = 'bar'

I don't get what you mean by 'Addends' either. How do you know which fields to look up for any given record?

basically you need to fully explain things, a digaram (as I said ) would probably help you clarify exactly what it is you need doing, and allow us to understand and help you

 

[gapper]

About the code, I haven't written it. A forum supplied me the basic code in order to make the sum among fields in different tables. I fit it, but I made a mistake amending them. 62 was my id element.

I know which fields to look up for any given record because I know the Addends name. E.g. Quantity1 in table1 is a field to sum because its name is Quantity1 and because it is in table1. Quantity will be for ever in table1.

About the diagram, when I will come out with a good idea for it, I attach it.

I tried the code with $sum ........ shown above. It seems not working. I am attaching the pictures in order to know where I am wrong.
Somma list shows quantity2 and sum2 with the same number. Sum2 works ok because quantity2 is into the same table.
Somma list shows also quantity1 and sum. Sum is not working maybe because quantity1 is in the same table as database join.
Please look at the pictures.

 

[rob]

Still confused
I'd much prefer you to give an example that used actual names and not table1 sum1 etc, these are not descriptive and don't allow us to understand the concept of what you want to do.

So for example :

I have a table called 'sales' which records sales in a record shop.
The list sales contains a field 'payment' which records the value of any sale made

There is another list called 'reports' which needs to sum all of the values of the field payment in the list sales.

 

[cheesegrits]

I too am still not understanding what you need to do. So that's three of us.

I know it's frustrating, when you know in your head what you need to do, but can't seem to make others understand. But that's half the battle with building applications. Often if you are having problems explaining it to other people, that means there is some logic flaw with what you are trying to do.

I think the main issue here is, we don't understand how the rows in the different tables you want to calculate totals from are "related". How do you know which rows in table1 and table2 to use, to get a total in table3?

There needs to be some kind of relationship (foreign key) between the rows.

-- hugh

 

[gapper]

Thanks for your words. I really appreciate them.
Sorry for my late reply.
Can I attach .xls or .xlsx files?

 

[gapper]

I am attaching some snapshots about what I need to do. Please let me know if it is clearer.
I need a way to write the ? element into the table sum which is a bank balance

 

[troester]

Still not very clear, I'll try to get it:
List1 (form1) contains multiple records of every user with bought items (e.g. records
user1 food 5
user1 tomato 5
user2 something-else 10
...

List2 (form2) ... sold items
user1 cheese 15
user2 xxx 20

List3 should show the balance(assuming both users starting with 40?)
user1 45
user2 50

Is this what you mean?