Using functions in Read Data Where Clause

go

gotpowr 11 Sep, 2024

I'm trying to figure out how to use functions in the Read Data Where Clause.

What I'm trying to do is something like:

"Where STR_TO_DATE(meeting_date,'%M/%D/%Y') >= CurrentDate"

I've tried in the regular where conditions but that doesn't seem to allow functions.

I've also tried adding a PHP where clause, but I wasn't sure of the format.

Return["STR_TO_DATE(meeting_date,'%M/%D/%Y')", "CURDATE",">="]

I could manually write the SQL using an "SQL" action, but I wasn't sure how to load that in a Model and reference it for my fields.

Max_admin 11 Sep, 2024

Answer

You can use SQL action and enable the form debug to find how the data is loaded and under which path in the vars, you may set the $this->data for any field to set that field value:

$this->data("field_name", "value", true); // works in v7

Your PHP condition syntax is wrong I think, you need 2 extra brackets around that:

return [["STR_TO_DATE(meeting_date,'%M/%D/%Y')", "CURDATE",">="]];

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

go

gotpowr 11 Sep, 2024

Sorry I should have clarified, I tried that and it didn't work.

I'm getting an error: OOPS! Incorrect parameter count in the call to native function 'STR_TO_DATE'

Even though I have debug on, I'm not seeing the results due to the error.

Is there a way to see the SQL that's being put together so I can debug it?

go

gotpowr 13 Sep, 2024

Hey Max

So I think I may have found an issue in general. It was happening (same error) in the PHP, and the read data.

Whenever I add a function with a " or ', it seems to error out. It's as if it's not parsing it correctly due to the quotes.

So even when i add a query fields function if I use DATE_FORMAT(datefield1, "%Y"), I get an invalid number of parameters. Same if I use single quotes, and the other methods above (I know query fields is in the select piece, but it's the same error and I was trying similar functions).

However, if I do COUNT(datefield1) then it works fine.

I'm thinking whatever php function is parsing together the SQL is reading the entries as strings and getting confused when it hits quotes.

Max_admin 14 Sep, 2024

then you may try using the SQL data action ? that is why it was added, for setting up complex queries

Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.

go

gotpowr 14 Sep, 2024

I tried, but could not get it to work with a dynamic drop down.

I used php to load it into a model and could see it from the debug, could reference a single row in a text (var:sqldata.0.model.0.field I believe) but would not work in the drop down. I selected the sqldata as the source of the dynamic options and tried numerous syntax like

Var:field name

Var:model.fieldname

Var:sqldata.model.fieldname

Var:sqldata.0.model.fieldname

Var:sqldata.model.0.fieldname

I included the proper brackets for all of them in the form, just left them off in the post here.

Forums

Using functions in Read Data Where Clause