Forums

Hi,

I am using Chronoconnectivity for filtering the table with the dropdown.

At the footer box I have written the code for sum the amount filed.

But the problem I face is I have the data's in amount field as "$1000,$2000"

When I sum this I get sum value as "$0".

Is there any option in Mysql to SUM the Amount filed with the currency symbol. Or Is there any other option to do this.

Regards,
Shoaib🙂

Hi Shaoib,

The best idea is to save the amount without the $ then you can do the arithmetic.

To make these add up you'll need to strip the $ sign off. Something like this should do it

<?php
$amount = (int) substr($MyRow->amount(1));
$total += $amount;
?>

Bob

Hi Bob,

Thanks For your reply. I have already Saved the amount with the currency symbol in my table.

Now I am trying to read the data's form the database.


The coding which I have used in the Footer Box is

<tr>
<td style="width: 5%;" align="center"></td>
<td style="width: 5%;"></td>
<td style="width: 25%;"></td>
<td style="width: 15%;">

<?php
$md2 =  JRequest::getVar('smd', '', 'post');
$result = mysql_query( "SELECT SUM(Donated_Amt) FROM Member_Donation WHERE select_transaction = '$md2' " )
or die("SELECT Error: ".mysql_error());

if($result != 0)
{
while($row = mysql_fetch_array($result,MYSQL_NUM)) 
{
//$amount = (int) substr($MyRow->$row[0]);
//$total += $amount;
//echo $total;
echo "SUM:".$row[0];
 }
}

?>
</td>
<td style="width: 15%;"></td>
<td style="width: 15%;"></td>
<td style="width: 10%;"></td>
<td style="width: 5%;"></td>
<td style="width: 5%;"></td>
</tr>
</table>

I don't know how to apply your method here. Can you please help me on this.


Regards,
Shoaib.🙂

Hi Shoaib,

Sorry it wasn't clear that was how you were getting the total. I don't know how you'd do it in MySQL. It might be possible using SUBSTRING and CAST.

Bob

Hi Bob,

I used the substring function "TRIM(LEADING 'x' FROM 'xxxbarxxx');". it works good.

Now I am planning to add the form below the dropdown filter.

I have no idea which one should I use for it "Chronoconnectivity" or "chronoform".

I have Attached The Screen shot Have a look in it and leave ur idea.

Note: This form Data should be stored in my database.

I have added submit and reset button below the form but it is not visible in the screenshot. Now I have used Chronoconnectivity to construct this page. Added the form coding in footer box.

But Now I am unable to store the data's.



Regards,
Shoaib

Hi shaoib,

Building the form in ChronoConnectivity if useful if you want to use the results in ChronoConnectivity - to filter the listing for example - but it doesn't have any of the functionality of ChronoForms.

If you want to email, save to the database. etc. then the form needs to be in ChronoForms.

The only way I can think of to easily put a form on the same page as a listing is to use a module on the page.

Bob

Hi Bob,

I can't use the module for Dropdown filter and the form. Because I am storing the data of that dropdown also in the database. Today I started it with the chronoform.

form coding

<h4 style='color:#234FA2;font-size:1.5em;line-height:1.25;margin-bottom:0.5em;'><b>Add Member Donation</b></h4>

<br />
<tbody>
    <tr>


 <?php
echo "<td>";
echo "<b>Select Transaction:</b><select name='smd' id='smd' onchange='submit()'>";
echo "<option value=0>Select Transaction</option>";
    
$result = mysql_query( "SELECT fabrik_internal_id, DATE_FORMAT(Transaction_Date,'%m-%d-%Y'),Credit_Amt,(SELECT CONCAT(Banks.Bank_Name) FROM Banks WHERE Banks.fabrik_internal_id = Bank_Transaction.Bank_Code) FROM Bank_Transaction WHERE Credit_Amt != ' '  AND Transaction_Type = 3  ORDER BY  Transaction_Date  DESC ,Org_Transaction_ID DESC LIMIT 1000 " )
or die("SELECT Error: ".mysql_error());

if($result != 0)
{
while($row = mysql_fetch_array($result,MYSQL_NUM)) {

echo "<option value='$row[0]'>$row[0] - $row[1] - $row[2] - $row[3]</option>";
 }
}


  echo "</select>";


/*echo "      <input type='button' value='GO!' height = '10px' width= '10px' onClick='submit()'  />";
*/
       echo"</table>";
echo "</td>";


   echo "<br /> <br />";




$val =JRequest::getVar('smd', '', 'post');
    
$result = mysql_query( "SELECT fabrik_internal_id, Donation_For_Year,Member_ID,Donated_Amt,Amt_Type,Payment_Method,Donated_On FROM Member_Donation WHERE select_transaction = '$val' " )
or die("SELECT Error: ".mysql_error());

if($result != 0 && $val !=0 )
{
echo "<table border=1 width= 100%>";

echo "<th style='width: 5%;'>Donation ID</th>";
echo "<th style='width: 5%;'>Donation Year</th>";
echo " <th style='width: 25%;'>Member</th>";
echo " <th style='width: 15%;'>Donated Amount</th>";
echo " <th style='width: 15%;'>Donated Amount Type </th>";
echo " <th style='width: 15%;'>Payment Method</th>";
echo " <th style='width: 10%;'>Donated On</th>";
echo " <th style='width: 5%;'></th>";
echo " <th style='width: 5%;'></th>";


while($row = mysql_fetch_array($result,MYSQL_NUM))
{
echo "<tr>";
echo "<td  style='width: 5%;'>".$row[0]."</td>";
echo "<td  style='width: 5%;'>".$row[1]."</td>";
echo "<td  style='width: 25%;'>".$row[2]."</td>";
echo "<td  style='width: 15%;'>".$row[3]."</td>";
echo "<td  style='width: 15%;'>".$row[4]."</td>";
echo "<td  style='width: 15%;'>".$row[5]."</td>";
echo "<td  style='width: 10%;'>".$row[6]."</td>";
echo "<td  style='width: 5%;'></td>";
echo "<td  style='width: 5%;'></td>";
echo "</tr>";
}
echo "</table>";
}
else
{
echo " No Value Found";
}




?>





<!-- container div starts here -->
<div id="add_form">
<!-- form div starts here -->
<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Donation Year</label>
    <select class="cf_inputbox validate-selection" id="select_0" size="1" title="Please Select Donation Year"  name="Donation_For_Year">
    <option value="">Choose Option</option>
      <?php    
$result = mysql_query( "SELECT fabrik_internal_id,Project_Year FROM Project_Year " )
or die("SELECT Error: ".mysql_error());

if($result != 0)
{
while($row = mysql_fetch_array($result,MYSQL_NUM)) {

echo "<option value='$row[0]'>$row[1]</option>";
 }
}
?>
    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Member</label>
    <select class="cf_inputbox validate-selection" id="select_1" size="1" title="Please Select Member"  name="Member_ID">
    <option value="">Choose Option</option>
     <?php    
$result1 = mysql_query( "SELECT First_Name,Last_Name,fabrik_internal_id FROM Member WHERE First_Name != '  ' AND Last_Name != '  '   ORDER BY First_Name,Last_Name ASC" )
or die("SELECT Error: ".mysql_error());

if($result1 != 0)
{
while($row2 = mysql_fetch_array($result1,MYSQL_NUM)) 
{
echo "<option value='$row2[2]'>$row2[0] $row2[1] - $row2[2]</option>";
 }
}
?> 
    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Reference Member</label>
    <select class="cf_inputbox validate-selection" id="select_2" size="1" title="Please Select Reference Member"  name="Reference_ID">
    <option value="">Choose Option</option>
      <?php    
$result1 = mysql_query( "SELECT First_Name,Last_Name,Member_ID FROM Committee WHERE Active = 0 ORDER BY Member_ID ASC" )
or die("SELECT Error: ".mysql_error());

if($result1 != 0)
{
while($row2 = mysql_fetch_array($result1,MYSQL_NUM)) 
{
echo "<option value='$row2[2]'>$row2[2] - $row2[0] $row2[1]</option>";
 }
}
?>
    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Sub Reference Member</label>
    <select class="cf_inputbox" id="select_3" size="1" title=""  name="Sub_Reference_ID">
    <option value="">Choose Option</option>
      <?php    
$result1 = mysql_query( "SELECT First_Name,Last_Name,fabrik_internal_id FROM Member WHERE First_Name != '  ' AND Last_Name != '  ' ORDER BY First_Name,Last_Name ASC" )
or die("SELECT Error: ".mysql_error());

if($result1 != 0)
{
while($row2 = mysql_fetch_array($result1,MYSQL_NUM)) 
{
echo "<option value='$row2[2]'>$row2[0] $row2[1] - $row2[2]</option>";
 }
}
?>

    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Event Name</label>
    <select class="cf_inputbox validate-selection" id="select_4" size="1" title="Please Select Event Name"  name="Event_Name">
    <option value="">Choose Option</option>
      <?php    
$result1 = mysql_query( "SELECT Event_Name FROM Event" )
or die("SELECT Error: ".mysql_error());

if($result1 != 0)
{
while($row2 = mysql_fetch_array($result1,MYSQL_NUM)) 
{
echo "<option value='$row2[0]'>$row2[0]</option>";
 }
}
?>
    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">Donated Amount</label>
    <input class="cf_inputbox required" maxlength="150" size="30" title="Please Enter Donated Amount" id="text_6" name="Donated_Amt" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Donated Amount Type</label>
    <select class="cf_inputbox validate-selection" id="select_7" size="1" title="Please Select Amount Type"  name="Amt_Type">
    <option value="">Choose Option</option>
     <?php    
$result1 = mysql_query( "SELECT Amt_Type FROM Amount_Type" )
or die("SELECT Error: ".mysql_error());

if($result1 != 0)
{
while($row2 = mysql_fetch_array($result1,MYSQL_NUM)) 
{
echo "<option value='$row2[0]'>$row2[0]</option>";
 }
}
?>
    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Payment Method</label>
    <select class="cf_inputbox validate-selection" id="select_9" size="1" title="Please Select Payment Method"  name="Payment_Method">
    <option value="">Choose Option</option>
     <?php    
$result1 = mysql_query( "SELECT payment_method FROM Payment_Method" )
or die("SELECT Error: ".mysql_error());

if($result1 != 0)
{
while($row2 = mysql_fetch_array($result1,MYSQL_NUM)) 
{
echo "<option value='$row2[0]'>$row2[0]</option>";
 }
}
?>
    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">Check Number</label>
    <input class="cf_inputbox required" maxlength="150" size="30" title="Please Enter Check Number" id="text_10" name="CheckNumber" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_datetimepicker">
    <label class="cf_label" style="width: 150px;">Donated On</label>
    <input class="cf_datetime required" title="Please Enter Donated On" size="20" id="Donated_On" name="Donated_On" type="text" />
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_datetimepicker">
    <label class="cf_label" style="width: 150px;">Sent Email On</label>
    <input class="cf_datetime" title="" size="20" id="Sent_Email_On" name="Sent_Email_On" type="text" />
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_textarea">
    <label class="cf_label" style="width: 150px;">Notes</label>
    <textarea class="cf_inputbox" rows="3" id="text_14" title="" cols="30" name="Notes"></textarea>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_button">
    <input value="Submit" name="button_15" type="submit" /><input type="reset" name="reset" value="Reset"/>
  </div>
  <div class="cfclear"> </div>
</div>
<!-- form ends here -->
</div>
<!-- div ends here -->

I have just added onchange event to the drop-down as onchange="submit()".

Now Drop-down filter works good but how will I be able to save the form data's to my database.

I tried by adding "Form Tag" but it doesn't help me.


Regards,
Shoaib.🙂

Hi shoaib,

I'm sorry, I'm not clear what the question is. You don't say where you are putting this code or what you want it to do. If it is a ChronoForm Form HTML then you can use a DB Connection to save the results to a Database table.

Bob

Hi Bob,


Please Have a look at Above Screen shot. Here I am trying to Have a form with Table filtering with a form dropdown.

As you have mentioned I can save the form data's with to database connection.

But here I am using function submit() onchange event of the Dropdown to filter the table.

And I should also use the submit button for saving the form data's to the database.

Will I be able to use More than one form tag in chronofrom. OR do you have any other good method to do it.

Regards,
Shoaib🙂

Hi shoaib,

I've looked at the screenshots and read the posts over again. I still can't get my head round what you are trying to do.

If you just want a list at the bottom of your form then create that in the Form HTML.

Bob

Hi Bob,

Can this be possible with CFv4? Any suggestion how to achieve it? I have a donation form that save all data to the db except for credit card information and I wanted to sum the total of all the donation based on the project selected. And like the previous posts, I wanted to sum all the donation in the same project name like below:
[attachment=0]Screen Shot 2012-04-17 at 7.09.08 PM.png[/attachment]
Can I include that in the same table where I save all the donation information or do I need to create a new table for this? Any advice or suggestion is very helpful. Thanks.

Hi jmarian1,

It doesn't make sense to me to save the totals to the same table as they aren't the same kind of record and they would need to be updated each time there was a new donation.

It's probably best to calculate them when you need them, you can use a MySQL query with SUM() to get the total for any query.

Bob

Hi Bob,

I tried your suggestion. This is the problem when you work in chronoforms, the regular mysql code doesn't work here. Any help please. Below is my code to sum the total donate amount:

<?php

// Get user object -information from Joomla
$project_name = JRequest::getString('project_name', '', 'post');
$db =& JFactory::getDBO();
$query = "
    SELECT  project_name, SUM(donate_amount_other)
        FROM `table` 
		WHERE `project_name` = '{$project_name}' 
        GROUP BY `project_name`
		";
$db->setQuery($query);
$data = $db->loadObjectList();
echo "hello";
echo $data->SUM(donate_amount_other);

echo '<p>Your current '.$data->project_name.' $ <input id="current_value" name="current_value" type="text" value="'.$data->SUM(donate_amount_other).'" readonly /></p>';

?>

but somehow it is giving me an error like below. It seems like the SUM() doesn't work in CFv4. Any suggestion please?

Fatal error: Call to a member function SUM() on a non-object in /home/auworg5/public_html/doe/administrator/components/com_chronoforms/form_actions/show_html/cfaction_show_html.php(136) : eval()'d code on line 15

Hi Bob,

Thanks but the error is gone. I am missing some quote in between. I just need to find out if the data show. I probably be back again. Thanks for your help!

Hi,

Just for other readers the query would need to include

SELECT  project_name, SUM(donate_amount_other) AS sum
. . . // and later
echo $data->sum;

for the code to work.

Bob

Thanks Bob for the suggestion. It now showing the total.

However, I have another problem. With the SUM() calculations, I create a condition that

Condition:

IF the SUM(TotalDonation) is greater than the project_cost, THEN error message will show and hide the form
ELSE show the form

Example: TotalDonation = 1900
project_cost = 12312

Somehow, that logic above does not work because if the condition is met, it is showing the error message and not showing the form like below:
[attachment=0]Screen Shot 2012-04-24 at 11.05.22 PM.png[/attachment]

Any help or idea or suggestions is very much appreciated please!!! Thanks.

My code and condition below:

SUM calculation and condition to show the form below.

<fieldset class="formfield">
<?php


// Get user object -information from Joomla
$project_name = JRequest::getString('project_name', '', 'get');
$project_cost = JRequest::getString('project_cost', '', 'get');
// Get user object -information from Joomla
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`donate_amount_other`) as sum, `project_name`
        FROM `table` 
		WHERE `project_name` = '{$project_name}' 
        GROUP BY `project_name`
		";
$db->setQuery($query);
$data = $db->loadObjectList();

foreach($data as $a) {
	echo '<div class="formfield">Current Total Donation Amount for Project '.$a->project_name.' you selected is <b><u>$'.number_format($a->sum).'</u></b></div>';
	echo '<input type="text" id="hidden_sum" name="hidden_sum" value="'.number_format($a->sum).'" />';
	echo '<p><em style="font-size:12px">Note: Please review the project you selected and enter your donation information below. </em></p>';

}
if (number_format($a->sum) > $project_cost) {
	echo 'Thank you for your donation but this project is now fully funded and work will be starting soon. Please click the arrow back button in your browser to select another project to donate. Thank you for your support!';
	echo '<div class="ccms_form_element cfdiv_submit multiline_add" id="submit_5_container_div">
<input type="button" name="form_back" id="form_back" value="Back" onclick="history.back()" /><div id="error-message-submit_5"></div></div>';
} else {
   
echo '<fieldset class="formsection" id="cc_doe" style="border:none !important;" >
  <!-- <legend class="hide">Donate....Helping School and Building Communities</legend> -->
  
  
  <div id="donate-credit_card" style="display:none">
  <p><em style="font-size:12px">Note: Please review the project you selected and enter your donation information below. </em></p>';
  
  // Get user object -information from Joomla
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`donate_amount_other`) as sum, `project_name`
        FROM `tablename` 
		WHERE `project_name` = '{$project_name}' 
        GROUP BY `project_name`
		";
$db->setQuery($query);
$data = $db->loadObjectList();
if (number_format($a->sum) > $project_cost) {
	echo 'The total donation amount for project you selected is greater than the project cost';
} else {

foreach($data as $a) {
	echo '<div class="formfield">Current Total Donation Amount for Project '.$a->project_name.' you selected is <b><u>$'.number_format($a->sum).'</u></b></div>';
	echo '<input type="text" id="hidden_sum" name="hidden_sum" value="'.number_format($a->sum).'" />';
	echo '<p><em style="font-size:12px">Note: Please review the project you selected and enter your donation information below. </em></p>';

}
}

 

//Show project information selected
$db =& JFactory::getDBO();
$query = "
    SELECT  *
        FROM `tablename` 
        WHERE `project_name` = '{$project_name}'
		";
$db->setQuery($query);
$data = $db->loadObjectList();
if (empty($data)) {
	echo "<p align='center'>Sorry, no project available for the school you selected</p>";
} else {

foreach($data as $d) { 
echo '<div class="formfield "><label>Project Name: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->project_name.'" name="project_name" id="project_name" readonly/></div>';
echo '<div class="formfield "><label>District Name: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->district.'" name="district" id="district" readonly /></div>';
echo '<div class="formfield "><label>School Name: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->SchoolName.'" name="SchoolName" id="SchoolName" readonly /></div>';
echo '<div class="formfield "><label>Principal Name: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->principal_name.'" name="principal_name" id="principal_name" readonly /></div>';
echo '<div class="formfield "><label>Category Level: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->category_code.'" name="category_code" id="category_code" readonly /></div>';
echo '<div class="formfield "><label>Project Code: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->project_code.'" name="project_code" id="project_code" readonly /></div>';
echo '<div class="formfield "><label>Project Description: </label><textarea id="project_description" cols="33" rows="3" width="60px;" class="" name="project_description" readonly>'.$d->project_description.'</textarea></div>';
echo '<div class="formfield "><label>Project Type: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->project_type.'" name="project_type" id="project_type" readonly /></div>';
echo '<div class="formfield "><label>Project Status: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->project_status.'" name="project_status" id="project_status" readonly /></div>';
echo '<div class="formfield "><label>Project Cost: </label><input maxlength="150" size="60" class="" title="" type="text" value="$'.number_format($d->project_cost, 2).'" name="project_cost" id="project_cost" readonly /></div>';
}
}


echo '<p><b style="color:#FF0000">*</b> <em style="font-size:12px">Required field<br />
Online donations can only be accepted via credit or debit cards. Your information will be treated with the strictest confidentiality.</em></p>
  <fieldset class="radiogroup" style="border:none !important;">
  <legend>Please specify the amount of money you would like to give.<b style="color:#FF0000">*</b></legend>
  <label>
  <input name="donate_amount" id="donate_amount25" class="radio required validate-one-required" type="radio" value="25" onClick="CalculateTotal(this.value,99);">
  $ 25</label>
  <label>
  <input name="donate_amount" id="donate_amount50" class="radio required validate-one-required" type="radio" value="50" onClick="CalculateTotal(this.value,99);">
  $ 50</label>
  <label>
  <input name="donate_amount" id="donate_amount100" class="radio required validate-one-required" type="radio" value="100" onClick="CalculateTotal(this.value,99);">
  $ 100</label>
  <label>
  <input name="donate_amount" id="donate_amount250" class="radio required validate-one-required" type="radio" value="250" onClick="CalculateTotal(this.value,99);">
  $ 250</label>
  <label>
  <input name="donate_amount" id="donate_amount500" class="radio required validate-one-required" type="radio" value="500" onClick="CalculateTotal(this.value,99);">
  $ 500</label>
  <label>
  <input name="donate_amount" id="donate_amount750" class="radio required validate-one-required" type="radio" value="750" onClick="CalculateTotal(this.value,99);">
  $ 750</label>
 <br />
 <br />
  <input id="donate_other" name="donate_amount" class="radio required validate-one-required" type="radio" value="other">
  Other 
    <label for="donate_amount_other">Other Amount: $</label>-->
<input id="donate_amount_other" name="donate_amount_other" class="text required validate-currency-dollar LV_valid_field" type="text" value="" onChange="CalculateTotal(this.value,99); ">
    <span class=" LV_validation_message LV_valid"> </span> <!-- </div> -->
  </fieldset>
  <fieldset id="donate_cc_type" class="radiogroup">
  <legend>Credit Card Type<b style="color:#FF0000">*</b></legend>
  <label>
  <input name="donate_cc_type" id="donate_cc_type" class="radio required validate-one-required" type="radio" value="visa">
  Visa</label>
  <label>
  <input name="donate_cc_type" id="donate_cc_type" class="radio required validate-one-required" type="radio" value="mastercard">
  Master Card</label>
  <label>
  <input name="donate_cc_type" id="donate_cc_type" class="radio required validate-one-required" type="radio" value="amex">
  AMEX</label>
  </fieldset>
  <div id="fld-donate_cc_number" class="formfield">
    <label for="donate_cc_number">Credit Card number:<b style="color:#FF0000;">*</b></label>
    <input id="donate_cc_number" name="donate_cc_number" class="text required validate-number" type="text" maxlength="19" value="" onChange="validatecard()">
  </div>
  
  <div class="formfield ">
    <label for="donate_cc_firstname">First Name:<b style="color:#FF0000">*</b> <span class="note_quest">(as shown on the credit card)</span></label>
    <input id="donate_cc_firstname" name="donate_cc_firstname" class="text required" type="text" value="">
  </div>
  <div class="formfield ">
    <label for="donate_cc_lastname">Last Name:<b style="color:#FF0000">*</b> <span class="note_quest">(as shown on the credit card)</span></label>
    <input id="donate_cc_lastname" name="donate_cc_lastname" class="text required" type="text" value="">
  </div>
  <div class="formfield">
    <label for="donate_cc_address">Address:<b style="color:#FF0000">*</b>  <span class="note_quest">(credit card billing address)</span></label>
    <input id="donate_cc_address" name="donate_cc_address" class="text required" type="text" value="">
  </div>
  <div class="formfield">
    <label for="donate_cc_city">City:<b style="color:#FF0000">*</b></label>
    <input id="donate_cc_city" name="donate_cc_city" class="text required" type="text" value="">
  </div>
  
<input type="hidden" name="form_description" id="form_description" value="Helping Schools and Building Communities Credit Card Form Transaction" />
    <input type="hidden" name="form_invoice" id="form_invoice" value="" />  

  
 
 </div>
 

<div class="ccms_form_element cfdiv_submit multiline_add" id="submit_4_container_div">
<input type="button" name="form_back" id="form_back" value="Back" onclick="history.back()" />  <input name="submit_4" id="submit_4" class="" value="Submit" type="submit" /><div id="error-message-submit_4"></div></div>';
}?>
</fieldset>

Hi jmarian1,

The number_format in if (number_format($a->sum) > $project_cost) { may be the problem, you end up comparing a string to a number.

If that isn't it then you need to add some debug lines to see exactly what is being compared here.

Bob

Hi Bob,

Does the "debugger" in the chronoform do the debug lines you are asking me to add to find out what it is exactly comparing to? Do you have other suggestion on how to compare a SUM() to a decimal field in database? Please advice. this is the only one I need to complete the form. Any suggestions or help is very much appreciated. thanks.

Hi Bob,

I tried something different to show the error message using javascript to hide and show the form. I also create a hidden textbox for the cost of the project from db table and another textbox for the total of the donation based on project selection to use it in JS code.

window.addEvent('domready', function() {
  ShowForm();
});

function ShowForm(){
	var a = document.getElementById('project_cost').value;
	var b = document.getElementById('hidden_sum').value;
	if (b > a){
		 alert("Thank you for your donation but this project is now fully funded and work will be starting soon. Please click the arrow back button in your browser to select another project to donate. Thank you for your support!");
			document.getElementById('show_errormessage').style.display=""; // this is for showing
			document.getElementById('show_ccform').style.display="none"; //hide
					
	} else {
			document.getElementById('show_ccform').style.display=""; // this is for showing
			document.getElementById('show_errormessage').style.display="none";
	}
	
}

Somehow the problem still exist. Why the two hidden textboxes cannot compare knowing that they are both decimal in the database. Below is my sql query for two different table with hidden textboxes as my output data.

Showing the SUM() based on donation amount per project name from a table

<?php
$project_name = JRequest::getString('project_name', '', 'get');
$project_cost = JRequest::getString('project_cost', '', 'get');
$hidden_sum = JRequest::getString('hidden_sum', '', 'post');

// Get user object -information from Joomla
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`donate_amount_other`) as sum, `project_name`, `project_cost`
        FROM `#__table` 
		WHERE `project_name` = '{$project_name}' 
        GROUP BY `project_name`
		";
$db->setQuery($query);
$data = $db->loadObjectList();

foreach($data as $a) {
	echo '<div class="formfield">Current Total Donation Amount for Project '.$a->project_name.' you selected is <b><u>$'.number_format($a->sum).'</u></b></div>';
	echo '<input type="text" id="hidden_sum" name="hidden_sum" value="'.$a->sum.'" />';
	
}

Showing the other data table with the project cost

$db =& JFactory::getDBO();
$query = "
    SELECT  *
        FROM `#__othertable` 
        WHERE `project_name` = '{$project_name}'
		";
$db->setQuery($query);
$data = $db->loadObjectList();
if (empty($data)) {
	echo "<p align='center'>Sorry, no project available for the school you selected</p>";
} else {

foreach($data as $d) { 
echo '<div class="formfield "><label>Project Name: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->project_name.'" name="project_name" id="project_name" readonly/></div>';
echo '<div class="formfield "><label>District Name: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->district.'" name="district" id="district" readonly /></div>';
echo '<div class="formfield "><label>School Name: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->SchoolName.'" name="SchoolName" id="SchoolName" readonly /></div>';
echo '<div class="formfield "><label>Principal Name: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->principal_name.'" name="principal_name" id="principal_name" readonly /></div>';
echo '<div class="formfield "><label>Category Level: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->category_code.'" name="category_code" id="category_code" readonly /></div>';
echo '<div class="formfield "><label>Project Code: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->project_code.'" name="project_code" id="project_code" readonly /></div>';
echo '<div class="formfield "><label>Project Description: </label><textarea id="project_description" cols="33" rows="3" width="60px;" class="" name="project_description" readonly>'.$d->project_description.'</textarea></div>';
echo '<div class="formfield "><label>Project Type: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->project_type.'" name="project_type" id="project_type" readonly /></div>';
echo '<div class="formfield "><label>Project Status: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->project_status.'" name="project_status" id="project_status" readonly /></div>';
echo '<div class="formfield "><label>Project Cost: </label><input maxlength="150" size="60" class="" title="" type="text" value="'.$d->project_cost.'" name="project_cost" id="project_cost" readonly /></div>';
}
}

?>

then I have this two <div> to show/hide

<div id="show_errormessage">
<fieldset class="formsection" id="cc_doe" style="border:none !important">
  <p>Thank you for your donation but this project is now fully funded and work will be starting soon. Please click the arrow back button in your browser to select another project to donate. Thank you for your support!</p>
</fieldset>
</div>  

<div id="show_ccform"> 
<fieldset class="formsection" id="cc_doe" style="border:none !important">
  <!-- <legend class="hide">Donate....Helping School and Building Communities</legend> -->
  
  
  <div id="donate-credit_card">
  <p><em style="font-size:12px">Note: Please review the project you selected and enter your donation information below. </em></p>
......some form here......
</fieldset>
</div>

Do you have other solution to make this work? I am guessing that the two hidden textboxes although are both decimal in the database, can't compare together. Any suggestion please? Thanks and hope to hear from you soon.

Hi jmarian1,

Try using parseInt() to force an integer value for the comparison so that you aren't comparing strings:

function ShowForm(){
   var a = $('project_cost').value;
   var b = $('hidden_sum').value;
   if ( parseInt(b) > parseInt(a) ){
. . .

Bob

Hi Bob. It is almost working but then a little problem that makes it not working completely. This may be a dummy question to everyone but I need to now the right way so I have to ask. Sorry.

How do you set the SUM() function in sql query result to zero (0) when there is nothing in the database? My code below:

<?php
$project_name = JRequest::getString('project_name', '', 'get');
$project_cost = JRequest::getString('project_cost', '', 'get');
$hidden_sum = JRequest::getString('hidden_sum', '', 'post');

// Get user object -information from Joomla
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`donate_amount_other`) as sum, `project_name`, `project_cost`
        FROM `#__table` 
      WHERE `project_name` = '{$project_name}' 
        GROUP BY `project_name`
      ";
$db->setQuery($query);
$data = $db->loadObjectList();

foreach($data as $a) {
   echo '<div class="formfield">Current Total Donation Amount for Project '.$a->project_name.' you selected is <b><u>$'.number_format($a->sum).'</u></b></div>';
   echo '<input type="text" id="hidden_sum" name="hidden_sum" value="'.$a->sum.'" />';
   
}

Hope you can help me. I am hoping this maybe my last question. Thanks!

Hi jmarian1,

I'm not quiet sure what the question is but this may be the answer:

foreach ($data as $a) {
  if ( !isset($s->sum) || !$a->sum ) {
    $a->sum = 0;
  }
  echo '<div class="formfield">Current Total Donation Amount for Project '.$a->project_name.' you selected is <b><u>$'.number_format($a->sum).'</u></b></div>';
  echo '<input type="text" id="hidden_sum" name="hidden_sum" value="'.$a->sum.'" />';
}

Bob

Hi Bob. Sorry for the confusion as well. My problem is when there is no donation amount in the database based on the project selected in the previous page of the form, the SUM() function is null therefore in the textbox that shows the total amount (supposed to be), doesn't show which creates error in my javascript code.

My question is, how can I show a "0" result in the textbox if the sum result is "null"?

I tried to copy and paste the code that you suggested but still didn't work. It is still giving me a problem. If the sum is null, the SUM() result in my textbox doesn't show that is why both of my <div> fields are showing which is not supposed to.:
[attachment=0]Screen Shot 2012-05-04 at 9.11.24 PM.png[/attachment]
Please help. Thanks.

Hi Jmarian1,

I'm sorry I don't know what divs these are or what needs to happen to them.

Bob

Hi Bob. Sorry. But how can I show in the textbox the result "0" if the SUM() function result is null or does not exist in the database? My code for my sql query:

<?php
$project_name = JRequest::getString('project_name', '', 'get');
$project_cost = JRequest::getString('project_cost', '', 'get');
$hidden_sum = JRequest::getString('hidden_sum', '', 'post');

// Get user object -information from Joomla
$db =& JFactory::getDBO();
$query = "
    SELECT SUM(`donate_amount_other`) as sum, `project_name`, `project_cost`
        FROM `#__table` 
      WHERE `project_name` = '{$project_name}' 
        GROUP BY `project_name`
      ";
$db->setQuery($query);
$data = $db->loadObjectList();

foreach($data as $a) {
   echo '<div class="formfield">Current Total Donation Amount for Project '.$a->project_name.' you selected is <b><u>$'.number_format($a->sum).'</u></b></div>';
   echo '<input type="text" id="hidden_sum" name="hidden_sum" value="'.$a->sum.'" />';
   
}

I just want to show "0" in my textbox below if there is no donation amount yet in the database that result to "NULL" or "EMPTY" result.

<input type="text" id="hidden_sum" name="hidden_sum" value="'.$a->sum.'" />

Hope I made it clearer. Please help. thanks.

Hi Jmarian1,

Isn't that the code I already posted here? If not, then I still have no idea what text box you are talking about - there is only a hidden input in this code.

Bob

Hi Bob. Thanks for checking. Yes, I tried the code you put it here but it didn't work. The <input ....> included here is the textbox I am talking about. Sorry for the confusion. Somehow, the code you included is not showing the 0 result still in the hidden input. Is there any other way or can I pm you again please?

Hi Bob, I finally get it. thanks a lot for your patience.

Hi jmarian1,

Glad too see you got it working :-)

Bob

PS I was away this week.