db query => row => array => values

Hi,

I am using an event switcher with a db query selecting a row
How can I get the data in the array into values? My code doens't want to work.
Looking for feedback/suggestions...

<?php
$options3 = array(
 'driver' => 'mysqli',
 'host' => 'localhost',
 'user' => 'aaa',
 'password' => 'bbb',
 'database' => 'ccc',
 'select' => true
);

$db3 =& JDatabase::getInstance($options3);
$query3 = $db3->getQuery(true);
$query3 = "
   SELECT Nr_Wkg, Person_name, Nr_group 
    FROM `ddd`
   WHERE `Person_id` = '{$form->data['id']}';
";

$db3->setQuery($query3);
$rows = $db3->loadObjectList();

foreach ($rows as $result3) {
$Nr_Wkg = $result3->Nr_Wkg;
$Person_name = $result3->Persone_name;
$Nr_group = $result3->Nr_group;
};

//MESSAGES
$errormsg ='<p>Hello $Person_name,<br /></p><p>you are not allowed to fill in this form.</p>';
$uitzmsg ='<p>You need to follow procedure x</p>';

//FILTER GROUPS NOT ALLOWED
if ( $Nr_group == 1010 ) {
$form->validation_errors['id'] = $errormsg;
  return 'fail';
} elseif ( $Nr_group == 3001 ) {
$form->validation_errors['id'] = $errormsg+$uitzmsg;
  return 'fail';
} elseif ( $Nr_group == 3012 ) {
$form->validation_errors['id'] = $errormsg+$uitzmsg;
  return 'fail';
} elseif ( $Nr_Wkg == 4 ) {
$form->validation_errors['id'] = $errormsg+$uitzmsg;
  return 'fail';
} elseif ( $Nr_Wkg == 5 ) {
$form->validation_errors['id'] = $errormsg+$uitzmsg;
  return 'fail';
} elseif ( $Nr_Wkg == 10 ) {
$form->validation_errors['id'] = $errormsg+$uitzmsg;
  return 'fail';
} elseif ( $Nr_Wkg == 20 ) {
$form->validation_errors['id'] = $errormsg;
  return 'fail';
} elseif ( $Nr_Wkg == 93 ) {
$form->validation_errors['id'] = $errormsg+$uitzmsg;
  return 'fail';
} else {
return 'success';
}

?>

Hi chuanse,

You probably need to add the values to the array:

foreach ($rows as $result3) {
  $form->data['Nr_Wkg'] = $result3->Nr_Wkg;
  $form->data['Person_name'] = $result3->Persone_name;
  $form->data['Nr_group'] = $result3->Nr_group;
};

Or you could use a DB Read action in place of your custom query.

Bob

Hi GreyHead,

There are some other issues (or should I say limitations) that had to be fixed before it worked... A DB Read action does not allow me to connect to database other then the Joomla-db itself. Since I have other databases that are consulted, I use "3" in all $...

Indeed, adding the db output to the form->data is one of the things I did. But for everyone else trying to do the same, here is how I finally did it:

In Events, first action in the On Submit event:

1) create Custom code to connect to the database, query the database and add values to form->data

<?php
$options3 = array(
 'driver' => 'mysqli',
 'host' => 'localhost',
 'user' => 'aaa',
 'password' => 'bbb',
 'database' => 'ccc',
 'select' => true
);

$db3 =& JDatabase::getInstance($options3);

//DO NOT USE getQuery => IT WILL NOT WORK AND THE DATA IS NOT PULLED FROM THE DB!!!
//Joomla tutorial states it should be present, but it will break the actual query following
//$query3 = $db->getQuery(true);

$query3 = "
   SELECT Nr_employer, Employer, Employee, Nr_group, Group 
    FROM `db_table`
   WHERE `Nr_employee` = '{$form->data['Employee_id']}'; //Employee_id was a form field and used to pull data from db
";

$db3->setQuery($query3);
$result3 = $db3->loadObject();
//now add the data from db to the form->data
$form->data['Nr_employer'] = $result3->Nr_employer;
$form->data['Employer'] = $result3->Employer;
$form->data['Employee'] = $result3->Employee;
$form->data['Nr_group'] = $result3->Nr_group;
$form->data['Group'] = $result3->Group;
?>

By using the custom code above I can use the variables as for example $form->data['Group'] in all following code

2) create Event switcher. I learned that event switcher only works with IF and ELSE. It does NOT work with ELSEIF. So given the code in the first post illustrating what I wanted to achieve I had to make 1 IF statement. I also learned that within an IF statement you can use the operator OR but not || which is actually the same.

<?php
//messages
$errormsg ='<p>Dear '.$form->data['Employee'].',<br /></p><p>The given employee ID is categorised under an group which is not allowed to use this form: ('.$form->data['Groep'].') .</p><p>U are not allowed to request an exception.</p>';

//FILTER THE NOT ALLOWED GROUPS

if ( $form->data['Nr_group'] == 1010 OR $form->data['$Nr_employer'] == 20  ) {
$form->validation_errors['Employee_id'] = $errormsg;
  return 'fail';
} else {
return 'success';
}

?>

3) Since I have 2 rule sets (where the errormsg is different) I needed to make a second event switcher
I also added some html/php in the errormsg itself to use form->data variables pulled from the db.

<?php
//message
$excepmsg ='<p>Dear '.$form->data['Employee'].',<br /></p><p>The given employee ID is categorised under an group which is not allowed to use this form: ('.$form->data['Groep'].') .</p><p>U are allowed to request an exception using <a href"#">this</a> form.</p>';

//FILTER THE NOT ALLOWED GROUPS

if ( $form->data['Nr_group'] == 3001 OR $form->data['Nr_group'] == 3012 OR $form->data['Nr_employer'] == 4 OR $form->data['Nr_employer'] == 5 OR $form->data['Nr_employer'] == 10 OR $form->data['Nr_employer'] == 93 ) {
$form->validation_errors['Employee_id'] = $excepmsg;
  return 'fail';
} else {
return 'success';
}

?>

And the bonus: Since we added all data from the db-query to the $form->data, for example $form->data['Employee'] , we can use this now with curly brackets in other places, in an email action within te same form: for example {Employee}

Hi chaunse,

You are right about the DB Read (and DB Multi Read) actions in CFv4 not supporting external databases. In CFv5 they are supported for Read and Save.

I'm not sure about ifelse () { or || - I don't remember having a problem with either. AFAIK the Event Switcher just parses the PHP it is given.

You can add more events to the Event Switcher in CFv4 (and in CFv5) - before you start adding code. Equally, using more than one works OK.

Bob

Hi GreyHead,

Very weird you do not have trouble to use elseif()... As soon i change this code

 if ( $form->data['Nr_group'] == 1010 OR $form->data['$Nr_employer'] == 20  ) {
$form->validation_errors['Employee_id'] = $errormsg;
  return 'fail';
} else {
return 'success';
}

To this variant

 if ( $form->data['Nr_group'] == 1010  ) {
$form->validation_errors['Employee_id'] = $errormsg;
  return 'fail';
} elseif ( $form->data['$Nr_employer'] == 20  ) {
$form->validation_errors['Employee_id'] = $errormsg;
  return 'fail';
} else {
return 'success';
}

... it does not "catch" the IF nor IFELSE and goes straight to the ELSE which is "success".
Any idea why that is?

Hi chuanse,

There's an extra $ in the ifelse line $form->data['$Nr_employer']

Bob