1. NOTICE: If you are using Fabrik and update to Joomla 3.10, you will need to update to Fabrik 3.10. And, if you are using Fabrik, do not upgrade to Joomla 4, we do not have a supported version ready for release. More information on a release date coming soon. Also, please note that Fabrik 3.10 will not install on any Joomla sites less than 3.8.
    Dismiss Notice

Executing Multiple Updates from element array?

Discussion in 'Community' started by bespokeappstt, Aug 24, 2021.

  1. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community
    I'm trying to execute multiple update statements as there is going to be multiple selections on the element in question. The element is a dbjoin and the user can select multiple items. Using a php form plugin, the issue I am having is that only the first Update statement works. The Update statement runs for only the first value in the array. Here is the code:
    Code (Text):

    // Get the db and the query
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);

    //Field to update
    $field = 'available = 0';

    $serialNumbers = $formModel->getElementData('rad_rentals___serial', true);
    foreach ($serialNumbers as $key => $serialID)
    {
      //Reference column
      $conditions = 'id' . '= '. $serialID ;
     
    }//end of foreach loop

       //Build SQL query and execute
        $query->update($db->quoteName('rad_serialassets'))->set($field)->where($conditions);
        $db->setQuery($query);
        $db->execute();
     
    So then I modified to below but now I get an error.
    Code (Text):

    // Get the db and the query
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);

    //Field to update
    $field = 'available = 0';

    $serialNumbers = $formModel->getElementData('rad_rentals___serial', true);
    foreach ($serialNumbers as $key => $serialID)
    {
      //Reference column
      $conditions = 'id' . '= '. $serialID ;

       //Build SQL query and execute
        $query->update($db->quoteName('rad_serialassets'))->set($field)->where($conditions);
        $db->setQuery($query);
        $db->execute();
     
    }//end of foreach loop

     
    How do I loop through the array of values in the element and execute multiple Update statements?
     
  2. startpoint

    startpoint Active Member

    Level: Community
    PHP:
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    // Fields to update.
    $fields = array(
        $db->quoteName('available') . ' = 0'
    );
    // Conditions for which records should be updated.
    $conditions = $db->quoteName('id') . ' IN (' . $serialNumbers . ')';
    $query->update($db->quoteName('rad_serialassets'))->set($fields)->where($conditions);
    $db->setQuery($query);
    $result = $db->execute();
    $serialNumbers must be commaseparated string.
     
  3. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community
    @startpoint Thank you very much. It works now. This is great. The new code is:
    Code (Text):

    // Get the db and the query
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);

    // Fields to update.
    $fields = array(
        $db->quoteName('available') . ' = 0'
    );

    $serialNumbersRaw = $formModel->getElementData('rad_rentals___serial', true);
    $serialNumbers = '';
    foreach ($serialNumbersRaw as $key => $serialID)
    {
      //Reference column
      $serialNumbers .= $serialID . ',' ;
     

    }//end of foreach loop

    $serialNumbers = rtrim($serialNumbers, ',');

    // Conditions for which records should be updated.
    $conditions = $db->quoteName('id') . ' IN (' . $serialNumbers . ')';
    $query->update($db->quoteName('rad_serialassets'))->set($fields)->where($conditions);
    $db->setQuery($query);
    $db->execute();
     
     
  4. bespokeappstt

    bespokeappstt Hubstaff freelancer

    Level: Community

Share This Page