My final hurdle - variable not showing in checkbox options

[thellie]

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

$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;

 

[achartier]

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.

 

[thellie]

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.

 

[cheesegrits]

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

 

[thellie]

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.

 

[cheesegrits]

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 ...

{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]

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

 

[thellie]

Works a treat, thanks