How to add together the amounts in two fields?

I have several fields that I set up in MySQL to be float so they can be currency without doing a lot of extra code to convert them to decimal numbers (feel free to tell me if I'm being too clever for my own good).

I have a set fee of $250. As people make their multiple payments over time or get credits, I want to show their new balance. I tried this:

<h3>Balance Due: <?php
$baldue=250-(
{camp_pay1+
{camp_pay2}+
{camp_pay3}+
{camp_pay4}+
{camp_fund1}+
{camp_fund2}+
{camp_fund3}+
{camp_fund4}+
{camp_fund5}+
{camp_fund6}+
{campership}); 
echo 'baldue';
?></h3>

But, clearly I have no idea what I'm doing. I would be very appreciative if you could help me with the correct syntax for this.

PS: Awesome program! Thank you.

Hi MyJC,

Where are you putting this code? If it's in the Form HTML then the {...} synatax won't work (it's only valid in the OnSubmit After boxes for use with the submitted values).

If it is the Form HTML then you will need an sql query to extract the informaton from the database table and you might as well do the adding there. It will be something like

$sql = "
  SELECT (camp_pay1 + camp_pay2 + . . .) AS `total` 
    FROM . . .;
  ";

Bob

Hi Bob;

Thanks for your help with this so far. I have the SQL worked out, I'm pretty sure, but not sure how to get it to actually display the results.

Yes, I'm putting it in the "Body" field in ChronoConnectivity "General Tab".

<p>Balace Due: <?php $sql="SELECT Round(250-(`camp_pay1` +`camp_pay2` +`camp_pay3` +`camp_pay4` +`camp_fund1` +`camp_fund2` +`camp_fund3` +`camp_fund4` +`camp_fund5` +`camp_fund6` +`campership` ),2)AS 'total' FROM 'jos_chronoforms_camp'"; ?></p>

The SQL returns the result I'm looking for in another report writer program, but I can't get it to display any results in ChronoConnectivity. I'm pretty sure I'm probably just not putting the php syntax together right.

Any help is appreciated.

Hi MyJC,

your code above is a SQL statement which doesn't run, you need 2 or 3 other code lines to run the query and get the result and show it in the place, code lines are joomla ones, here is an example:

$query = "SELECT * FROM #__table WHERE id = '".$row->id."' ORDER BY id";
$database->setQuery( $query );
echo $result = $database->loadResult();

Regards
Max

Hi MyJC,

To add to Max's post - you also don't need to do the arithemtic in the SQL - it might be simpler to load all the fields from the record and do the calculations in PHP. You have more flexibility that way.

Bob

Thank you both for helping with this.

Bob - you're confusing me. Doing the math in PHP was what I was trying to do in the first place and you said,

sql query to extract the informaton from the database table and you might as well do the adding there

so ... now I'm confused.

Is it a matter of better performance to do the math in SQL and more control doing it in PHP? If so, I'll stay with SQL.

Otherwise, please review the thread and advise, if you would be so kind, please, how do I do the math in PHP in ChronoConnectivity?

I'm sorry if I'm being stupid and tripping over the answer right in front of my nose. I really appreciate your kind assistance and patience with me.

Hi MyJC,

your code above is a SQL statement which doesn't run, you need 2 or 3 other code lines to run the query and get the result and show it in the place, code lines are joomla ones, here is an example:

$query = "SELECT * FROM #__table WHERE id = '".$row->id."' ORDER BY id";
$database->setQuery( $query );
echo $result = $database->loadResult();

Regards
Max

Thanks Max for your reply. The thing is - do I have to repeat that information? It seems like ChronoConnectivity is already supplying some of those pieces. I think I'm missing something in translation, so let me show you a screen shot:

Hi MyJC,

Apologies, my bad - trying to answer too many posts and losing the plot on some of them :-(

Let's backtrack a bit and work out what you are trying to achieve. Do you want to show the results for one user or for a list of them? I assume a list as you are using ChronoConnectivity.

In that case you are probably limited to the PHP approach as - if I recall correctly - ChronoConnectivity doesn't give you easy access to the SQL except for the WHERE and ORDER BY clauses.

Bob

Hey Bob:

No worries! Been there - done that. I'm just grateful you're willing to help.

I'm trying to show the results of one entry (not just one user - the same user could have made multiple entries).

If you look at the screen shot above, you can see that I'm doing that in the WHERE field and it's working. I can get just the fields from just the entry that I'm looking for.

So, I'm trying to do math on fields in the entry -- add them up. I can get this with the sql statement that I quoted previously in BrightcodeReporter - so I really thought I was on the right track, but it doesn't work with ChronoConnectivity so there is something CC is doing in the background (I assume / surmise) that is impeding me.

(for those that then ask, why not just use BrightcodeReporter - it works great for the admin, but doesn't format well in the user interface - I love CC for that side).

Thanks again for your help,

Walt (aka MyJC)

Hi Walt,

I'm going back to your first post with a couple of corrections (added a missing '}' and replaced 'baldue' with a formatted $baldue). This code should go into the body and will then show once for each record selected in the WHERE box.

<?php
$baldue = 250 - (
{camp_pay1} +
{camp_pay2} +
{camp_pay3} +
{camp_pay4} +
{camp_fund1} +
{camp_fund2} +
{camp_fund3} +
{camp_fund4} +
{camp_fund5} +
{camp_fund6}  +
{campership} );
$baldue = sprintf('%01.2f', $baldue);
echo "<h3>Balance due: ";
echo sprintf('%01.2f', $baldue);
echo "</h3>";
?>

Bob

Thanks for trying, Bob, but I get

Parse error: syntax error, unexpected '{' in /home/cbcborg/public_html/youth/components/com_chronoconnectivity/chronoconnectivity.html.php(176) : eval()'d code on line 5

with that.

It doesn't seem to like the curly brackets in the php code.

Max:

I tried your suggestion and came up with this ...

$query = "SELECT `camp_pay1` +`camp_pay2` +`camp_pay3` +`camp_pay4` 
+`camp_fund1` +`camp_fund2` +`camp_fund3` +`camp_fund4` +`camp_fund5` 
+`camp_fund6` +`campership` FROM 'jos_chronoforms_camp' WHERE cf_id = 
'pmnts'";
$database->setQuery( $query );
echo 'Balance Due: ';
echo $result = $database->loadResult();
?>

I think that's right. But, nothing displays next to Balance Due;

Hi MyJC,

try to echo the query in the header and run it in ppmyadmin and see if it gets you any results or what error do you get ?

I prefer to load the fields data into an Object and run a PHP loop to sum them up unless I'm sure that the query will run fine all time!

Regards,
Max

The SQL works in phpmyadmin as long as I remove the ticks around jos_chronoforms_camp and remove the entire "WHERE" since I'm using this little trick (learned elsewhere in this forum)"

WHERE cf_id = "<?php echo JRequest::getVar('pmnts'); ?>"

to get the WHERE from a previous CC page, which I don't know how to duplicate for this exorcise. When I load this in the header it doesn't error, but it doesn't give me anything either. When I add another field (eg "student_name") to the query as a test that it's getting and printing variables, it reports back the entry in that field in the first row of the database, but nothing else. So, clearly it's not getting the WHERE right and it's not picking it up from the CC "SQL Where" field.

You said,

I prefer to load the fields data into an Object and run a PHP loop to sum them up unless I'm sure that the query will run fine all time!

I don't know what you mean by that and therefore, I don't know how to do that. I just want to add items from several variables on one row. I'm not adding the same field/column from various rows ... just in case that's what you mean.

Hi MyJC,

if you write in the header :

WHERE cf_id = "<?php echo JRequest::getVar('pmnts'); ?>"

then it should show you what the WHERE statement is, like :

WHERE cf_id = "34"

so you know if there is a correct WHERE will run which will load any number of rows!

Max