Forums

Hi. I need to connect my form to the db table based on selection and login user but not sure how to fulfill it. This will go to the CSSV. I wanted to get the sum of a column based on login user and selected drop down category. My table consist of category, login user, project_cost, donate_amount. With the login user,

if the user's category selected in the form does exist in the database table
get the total amount of donate_amount based on category and user from the table then
check if total donate_amount is equal to the total project_cost
if total donate_amount is => project_cost
allow user to continue
else
do not allow to continue
show error message

I have a code here but somehow it doesn't connect to db. Please help. Thanks.

$user=& JFactory::getUser();
$name=$user->name;
$donate =& JFactory::getDBO();
$query = "
        SELECT `principal_name`, SUM(`donate_amount`) as subtotal, `category_code`
       FROM `#_chronoforms_creditcard`
       WHERE `principal_name` = '{$principal_name}' && `category_code`
       GROUP BY `principal_name`, `category_code`
       ";
$donate->setQuery($query);
$info = $donate->loadObjectList();
if (empty($info)) {
	$a->subtotal = "0.00";
	echo '<div class="formfield">Current Total Donation Amount for Principal '.$name.' you selected is <b><u>$0.00</u></b></div>';
	echo '<input type="text" id="hidden_sum" name="hidden_sum" value="'.$a->subtotal.'" />';

} else {
	foreach($info as $a) { 
    
echo '<table><tr><th>User Name</th><th>Category Code</th><th>Total</th></tr><tr><td>';
echo $a->name;
echo '</td><td>';
echo $a->category_code;
echo '</td><td>';
echo $a->subtotal;
echo '</tr></table>';
}
}

Hi jamrian1,

This line is incomplete

WHERE `principal_name` = '{$principal_name}' && `category_code`

You need to test `category_code` as equal to something - but what ???

Bob

Hi Bob,

Thank you for your fast reply. But somehow, adding the missing piece below with your response:

Hi jamrian1,

This line is incomplete

WHERE `principal_name` = '{$principal_name}' && `category_code`

You need to test `category_code` as equal to something - but what ???

Bob

I added the

`category_code`='{$category_code}'

it still doesn't get the right info. I check the database with the login user & category_code and manually total, it is not empty. Somehow it is not getting the total but instead it is "0". I don't know where my code get wrong. It works in the other form but not this new form. Any idea? Can I do this using the Multi Record Loader? I just need to get the total of the donate_amount based on principal_name & category_code but it is giving me a hard time.

Hi jmarian1,

Further on in the code $a seems to appear out of thin air?? Where is that defined? It might be $info but $info is an array not an object.

I think that you may need to debug your code lien by line to make sure what variables you have and what their values are.

Bob

Hi Bob,

With the initial post above, the code is working now (thanks to your help), but my problem is not totally solve yet. I have a validation that I wanted to work below either showing the validation error or continue to save data based on the db connect in my initial post.

this validation are all work in the CSSV and below is my validation code that I wanted to complete but somehow the error message is not passing correct assuming that all of the form are declared

<?php

if ($project_cost > $hidden_sum) {
$db =& JFactory::getDBO();
$query = "
    SELECT  `principal_name`, `category_code`
        FROM `#__chronoforms_data_principal_project` 
        WHERE `principal_name` = '{$principal_name}' && `category_code` = '{$category_code}' ";
$db->setQuery($query);
if ($info = $db->loadObjectList() ) {
  $form->validation_errors['category_code'] = 'Sorry, the Category you selected already exists in the database. You are allowed to enter only one project for each category.';
    $valid =false;
} else {
   $valid = true;
}

//check if category level and project cost is equal
if (!empty($category_code) && (!empty($project_cost)))
         { 
             $project_cost = str_replace(',','',$project_cost);
	     $project_cost= str_replace('-','',$project_cost);      
               if (!preg_match('/^\s*\$?\s*(\d+(\.\d{0,2})?)\s*$/',$project_cost)) 
		{
      			$form->validation_errors['project_cost'] = 'Invalid project cost amount. The project cost amount only accepts digits 0 to 9 and a decimal point. Please verify the amount and delete any comma entered.';
    			$valid =false;
		}
        else
        {

                  if ($category_code == "1-999" && ($project_cost < "1" || $project_cost > "999"))
                    {
                        $form->validation_errors['project_cost'] = 'You selected Level 1: 1-999 in the Category Level. Project estimated cost amount should not be less than $1 and greater than $1,000. Please revise your entry or use the right Category Code.';
                        $valid =false;
                    }
                    elseif ($category_code == "1000-9999" && ($project_cost < "1000" || $project_cost > "9999"))
                    {
                         $form->validation_errors['project_cost'] = 'You selected Level 2: 1000-9999 in the Category Level. Project estimated cost amount should not be less than $1,000 and greater than $9,999. Please revise your entry or use the right Category Code.';
                        $valid =false;
                   }
                  elseif ($category_code == "10000-149999" && ($project_cost < "10000" || $project_cost > "149999"))
                   {
                        $form->validation_errors['project_cost'] = 'You selected Level 3: 10,000-149,000 in the Category Level. Project estimated cost amount should not be less than $10,000 and greater than $149,999. Please revise your entry or use the right Category Code.';
    	               $valid =false;
                   }
                  elseif ($category_code == "250000" && ($project_cost < "250000" || $project_cost > "99999999"))
                   {
                          $form->validation_errors['project_cost'] = 'You selected the Dream Project: 250,000+ in the Category Level. Project estimated cost amount should not be less than $250,000 and greater than $9,999,999.00. Please revise your entry or use the right Category Code.';
    	                  $valid =false;
                   }
             }
   } 
} else {
	   $valid=true; //allow to save data to db
}
return $valid; //allow to save data to db

?>

but somehow the validation about the category if exist still show if the hidden_sum is greater than the project_cost. Please help. Thanks.

Hi jmarian1,

This is just a case for straightforward debugging. The code looks correct so you need to check what values you have in $project_cost and $hidden_sum and make sure that they are valid numbers - otherwise PHP will do a string comparison.

Bob