• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Set a status field depending on an value of an other table

wuschel_lux

Member
Hi all,
I am trying again to do something new for me.

E.g. a person wants to subscribe to a course, I have a field (dropdown element) only visible for Admins with the status. In the table courses, there is a field with the max. participants in.
So when someone subscribes and the already registered amount is <= the mac amount of participants then set the status value to "accepted", if over the limit set to "waiting list".

I suppose it is possible, as nearly everything ist possible with this fantastic tool, just need some help to achieve this.

Many thanks for your support.
BR
 
Hey sound good. Just started to read the wiki of the php form plugin. I think this is a very flexible and powerful tool.
Thanks!
 
So finally I managed the job with 2 sql queries and then update the dropdown element field as described in the wiki.
PHP:
// SQL Querries
// ...

if($regPlaces >= $maxPlaces){
  $formModel->updateFormData('fkm_register_course___status', 'waiting list', true);
} else {
  $formModel->updateFormData('fkm_register_course___status', 'confirmed', true);
}

I have just one concern. I have a so called Admin Area (Fabrik Group), hidden from public/registered users. Only Event Validators (Joomla Group) have access to this Group to validate/update the status, the status field is no more populated but is NULL.

How ca I populate it while saving the record but hide it from groups having non Admin access.
Thanks for your help, I'm a bit stuck on this.

BR
 
If I understand you correctly, the PHP form plugin with your code doesn't work for public/registered users because only Event Validators (EV) have access to the status element, and so it is still NULL once an EV looks it up?
This would mean the status field is not part of the form data, and then you'll need to replace the $formModel->updateFormData lines with PHP SQL queries to update the field.

Hint: to avoid it also "auto-happening" for EVs, you may want to restrict the execution only to public/registered users by wrapping your code into a condition equivalent to "if my user group is not EV"...
 
Thanks lousyfool,
I will update the DB then with a SQL query and php form plugin code is just executed on a new submitted record, so later when EV are entering the record to modify fields the status field is not re-evaluated.
BR
 
Hi again, I am facing problems with the SQL Update query:
PHP:
  $db = JFactory::getDbo();
  $query = $db->getQuery(true); 
    $query->clear();
    $query->update('table');
    $query->set('status = "waiting list"');
    $query->where('course = ' . $courseID);
    $db->setQuery($query);
    $db->execute();
The value stored in DB is NULL.

Is the query wrong or maybe the time when the php code is triggered. I tried with:
- End of form submission
- After Data stored, ...

Is there a possibility to echo the query when populated to see possible errors?

Thanks
 
Try
Code:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->update($db->quoteName('table'));
$query->set($db->quoteName('status') . ' = ' . $db->quote('waiting list'));
$query->where($db->quoteName('course') . ' = ' . $courseID);
$db->setQuery($query);
$db->execute();

Is there a possibility to echo the query when populated to see possible errors?
Search the forum for "fabrikdebug".
 
You can do
echo $query;exit;

Where is $courseID coming from?

BTW: don't use $db etc (this may conflict with Fabrik variables), use something like $mydb
 
Thanks again, there is an update done, but I have to fine tune a bit.

The ?fabrikdebug=1 was enabled but I did not find the "php form plugin" query in the debug accordion.

Where is $courseID coming from?
PHP:
// get course ID
$courseID = $formModel->getElementData('fkm_register_course___course', true);
$courseID = is_array($courseID) ? $courseID[0] : $courseID;
 
The ?fabrikdebug=1 was enabled but I did not find the "php form plugin" query in the debug accordion.

Nope, you won't see your queries there. As Troester said, you'll have to dump it by hand and exit.

var_dump((string)$query);exit;

... which should just echo your query to the browser then quit.

-- hugh
 
Hi all,

I discovered now a strange behavior in my update php form script.

There are for a course 2 places available I have 2 different behaviors:
a. Logged in with Super User rights
- the first 2 subscriptions were 'accepted' and from the 3rd on get the status 'waiting list'
b. Logged in without Super User rights
- the first 3 subscriptions were 'accepted' and from the 4th on get the status 'waiting list'

Here the complete code I tried to update the status field:
PHP:
// get course ID
$courseID = $formModel->getElementData('fkm_register_course___course', true);
$courseID = is_array($courseID) ? $courseID[0] : $courseID;

// get register ID
$regID = $formModel->getElementData('fkm_register_course___id', true);

// Get nbr places
$mydatabase =& JFactory::getDBO();
$sql = "SELECT places
  FROM fkm_courses
  WHERE status = 'online'
    AND id = '$courseID'
";
$mydatabase->setQuery( $sql );
$rows = $mydatabase->loadObjectList();
  foreach ( $rows as $row ){
        $maxPlaces = $row->places;
    }

// Count already registered
$sql1 = "SELECT COUNT(id) AS Reg
  FROM fkm_register_course
  WHERE status = 'confirmed'
    AND course = '$courseID'
";
$mydatabase->setQuery( $sql1 );
$rows1 = $mydatabase->loadObjectList();
    foreach ( $rows1 as $row1 ){
        $regPlaces = $row1->Reg;
    }

$mydb = JFactory::getDbo();

if($regPlaces > $maxPlaces){
  $query = $mydb->getQuery(true);
  $query->update($mydb->quoteName('fkm_register_course'));
  $query->set($mydb->quoteName('status') . ' = ' . $mydb->quote('waiting list'));
  $query->where($mydb->quoteName('id') . ' = ' . $regID);
  $mydb->setQuery($query);
  $mydb->execute();
} else {
  $query = $mydb->getQuery(true);
  $query->update($mydb->quoteName('fkm_register_course'));
  $query->set($mydb->quoteName('status') . ' = ' . $mydb->quote('confirmed'));
  $query->where($mydb->quoteName('id') . ' = ' . $regID);
  $mydb->setQuery($query);
  $mydb->execute();
}

Hope there is someone out could help me, I am getting crazy.
Thanks in advance for your support.
BR
 
What does that $maxPlaces code do?

At the moment, it'll just get set to the value of the last row you selected (and you have no ordering on that query, so no guarantee what order they will select in). Not sure if that's what you need, or if you are trying to find the largest number out of the selected rows.

-- hugh
 
thanks to looking in
What does that $maxPlaces code do?
$maxPlaces is a field in the courses table (all couses are listed here with title, descr. start and end time, ... , and Max participants) and contains an integer from 1 to 100 and should limit the amount of participants in this course (the id [here course id] is unique).
 
Right, so is this loop:

Code:
  foreach ( $rows as $row ){
        $maxPlaces = $row->places;
    }

... supposed to find the largest number in the selected rows? As written, $maxPlaces will have the value of whatever the last row in the selected rows was. If you wanted to find the largest in the selected rows, you'd have to do ...

Code:
$maxPlaces = 0;
foreach ( $rows as $row ){
   if ((int)$row->places > $maxPlaces)
   {
      $maxPlaces = (int)$row->places;
   }
}

Although I'm not sure if your query selects more than one row or now.

-- hugh
 
We are in need of some funding.
More details.

Thank you.

Members online

No members online now.
Back
Top