Database join via calc-element many data-inputs

  • Views Views: 1,112
  • Last updated Last updated:

Navigation

      Access element (+)
      Birthday element
      Button element
      Calculation element
      Captcha element
      Checkbox element
      Colour Picker element
      Count element (+)
      Database join element
      Date element
      Digg element
      Display text element
      Dropdown element
      Facebook Like element
      Field element
      File Upload element
      Folder element
      Google Map element
      Image element
         Image databese join
      Internal id element
      IP element
      J!Date element
      Kaltura element
      Link element
      Notes element
      OpenStreetMap element
      Picklist element
      Radio Button element
      Rating element
      Sequence element
      Slider element
      Tags element
      Textarea element
      Thumbs element
      Time element
      Timer element
      Timestamp element
      Total element
      User element
      User group element
      Video element
      View level element
      YesNo element
      Youtube element
      Akismet validation
      Is Email validation
      Is Not validation
      Is Numeric validation
      Not empty validation
      PHP validation
      Rsa id
  • Introduction
    Imagine there are two tables:
    • Tb_roof_photos (table1)
    • Tb_window_photos (table2)
    Question
    How can you automatically join a roof-photo with a window-photo?
    You want to link photos that are from the same housename, location, period, etc.

    Create batches
    You can make a calc-element like this for table1:

    $street = '{table1___street_raw}';
    $date = '{table1___date_time}';
    $date = date("ymd",strtotime($date));
    $house = '{table1___namehouse_raw}';
    $vvvv = ($date. $species. $userID .$town1);

    return crc32($vvvv);

    Than like such you can do for table2 as well in order to generate an equal batch-value.

    Compare batches

    $db = FabrikWorker::getDbo($loadJoomlaDb = false, $cnnId = 11);
    $query = $db->getQuery(true);
    $query->select('window_photo')->from('table2')
    ->where('batch ='. (int)'{table1___batch}');
    $db->setQuery($query);
    $results = $db->loadResult();

    if ($results == "")
    {return 'Add a <a href="index.php/windows">Window Photo</a> that matches the location of your roof';}
    else {return $results;}

    Limiting user-input
    UserID, DateTime, and StreetName can be automatically updated by users. Streetname can be retrieved from the field-element and set autocomplete to Geocomplete in combination with the googlemap-element.

    Result
    If the farmer opens the form a roof photo of the roof can be uploaded and the photo of the window will be automatically added if the autocomplete data that are used to create the batch match with the batch of the other table.
Back
Top