1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice
  2. If you update to Joomla 3.9.16, you will have to update Fabrik from the latest GitHub version, to fix a bug introduced in Joomla, which makes it impossible to list front end folder locations in backend JForms. This affects things like template selection.
    Dismiss Notice

My final hurdle - variable not showing in checkbox options

Discussion in 'Community' started by thellie, Jul 30, 2019.

  1. thellie

    thellie Member

    Level: Community
    Crack this and I'm ready to go live...

    This is a checkbox element.

    I want to pull in the names of the volunteers who would teach in a given lesson. The query pulls the names of those who are available (ie lesson date is between their start and finish dates).

    But I can't get the query to pull in the variable - $lessondate. It works with a static date (ie '2019-02-02 00-00-00') so I know it's an issue with the variable $lessondate... but I've tried all the variations I can think of - long names, short names, (INT), _raw, new datetime(), etc etc...

    At the moment I am using the second piece of code, which pulls the names of those currently onsite - it'll do but I'd prefer the first option if possible.

    If I get this done, I can put the computer down for a while, to stop getting vertigo & vomiting from staring at the screen all day and night :eek:

    Code (Text):

    $mydb = FabrikWorker::getDbo();
    $lessondate= '{lessonplans___lesson_date}';

    $mydb->setQuery(
    "SELECT user_id, CONCAT(first_name,' ',last_name) AS teacher FROM vol_appform
    WHERE
    ((SELECT DISTINCT lesson_date FROM lessonplans WHERE lesson_date = '$lessondate') BETWEEN
    startdate AND IF(actual_enddate,actual_enddate,enddate)) AND
    (programme = 19 || programme = 20)
    ORDER BY first_name
    ");
    $rows = $mydb->loadObjectList();
    foreach ($rows as $row) {
        $options[] = JHTML::_('select.option', $row->user_id, $row->teacher);
    }
    return $options;

    =========

    $mydb = FabrikWorker::getDbo();
    $lessondate= '{lessonplans___lesson_date}';

    $mydb->setQuery(
    "SELECT user_id, CONCAT(first_name,' ',last_name) AS teacher FROM vol_appform
    WHERE
    (CURDATE() BETWEEN
    startdate AND IF(actual_enddate,actual_enddate,enddate)) AND
    (programme = 19 || programme = 20)
    ORDER BY first_name
    ");
    $rows = $mydb->loadObjectList();
    foreach ($rows as $row) {
        $options[] = JHTML::_('select.option', $row->user_id, $row->teacher);
    }
    return $options;
     
     
  2. achartier

    achartier Active Member

    Level: Community
    A couple of things you can do to try and isolate the issue. Build the query as a string first then echo $query; exit; and look at the resulting query and make sure you get what you expect.

    Second, install the component mijosql and copy and paste the query into the mijosql box and see what results you get. You can play with the query in mijosql until it works correctly for you then copy back the correct query to your code.
     
  3. thellie

    thellie Member

    Level: Community
    Thanks, I used to have MijoSQL on another site, so am familiar with it... will let you know how I get on

    The thing is that the query with the $lessondate replaced with a string works perfectly already.
     
  4. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    This is running in the advanced "eval populate" for a checkbox?

    I don't think element placeholders would be available when that code is run.

    -- hugh
     
  5. thellie

    thellie Member

    Level: Community
    Yes Hugh, it is...

    Ah, okay, if that's the case I'll leave it for now. I have checking for anomalies and quirks to go through as we start using the new website, so more than enough to do for now.
     
  6. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Personally I'd use a join element set to checkbox, joined to the volunteers table, which I assume is vol_appform, and use a WHERE clause on that, using a dependent subquery that uses that placeholder, and enable AJAX for it.

    So the query would be something like ...

    Code (Text):

    {thistable}.id IN (SELECT id FROM vol_appform
    WHERE
    ((SELECT DISTINCT lesson_date FROM lessonplans WHERE lesson_date = '{lessonplans___lesson_date}') BETWEEN
    startdate AND IF(actual_enddate,actual_enddate,enddate)) AND
    (programme = 19 || programme = 20)
    ORDER BY first_name)
     
    -- hugh
     
    thellie likes this.
  7. thellie

    thellie Member

    Level: Community
    Thanks for spending some time on this Hugh, it's very much appreciated. I have some amends and requests to do first, then a full backup. After that I'll give this a go and let you know how I get on :)
     
  8. thellie

    thellie Member

    Level: Community
    Works a treat, thanks :)
     
    cheesegrits likes this.

Share This Page