1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

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

Discussion in 'Community' started by wuschel_lux, Jul 12, 2019.

  1. wuschel_lux

    wuschel_lux Member

    Level: Community
    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
     
  2. lousyfool

    lousyfool Active Member

    Level: Community
  3. wuschel_lux

    wuschel_lux Member

    Level: Community
    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!
     
  4. wuschel_lux

    wuschel_lux Member

    Level: Community
    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
     
  5. lousyfool

    lousyfool Active Member

    Level: Community
    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"...
     
    wuschel_lux likes this.
  6. wuschel_lux

    wuschel_lux Member

    Level: Community
    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
     
  7. wuschel_lux

    wuschel_lux Member

    Level: Community
    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
     
  8. lousyfool

    lousyfool Active Member

    Level: Community
    Try
    Code (Text):
    $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();
    Search the forum for "fabrikdebug".
     
    wuschel_lux likes this.
  9. troester

    troester Well-Known Member Staff Member

    Level: Community
    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
     
    wuschel_lux likes this.
  10. wuschel_lux

    wuschel_lux Member

    Level: Community
    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.

    PHP:
    // get course ID
    $courseID = $formModel->getElementData('fkm_register_course___course', true);
    $courseID = is_array($courseID) ? $courseID[0] : $courseID;
     
  11. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  12. wuschel_lux

    wuschel_lux Member

    Level: Community
    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
     
  13. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  14. wuschel_lux

    wuschel_lux Member

    Level: Community
    thanks to looking in
    $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).
     
  15. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Right, so is this loop:

    Code (Text):

      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 (Text):

    $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
     

Share This Page