Forums

Syntax for SQL Where clause in CF8 Table Listing fields

rsearle 11d ago

It looks like your intention for the Where clause in the Table Listing field was to evaluate column values against a URL query parameter passed to the form from a menu item, or a crafted link to the form. That sounds OK, but I can't figure out the syntax to get the Where clause to work. For example, in the CF8.0.21 Table listing demo form, I would like to express something like:

{row:title} LIKE '%{data.quote:search_string}%'

... where title is a column in the data source table, and search_string is a query parameter passed in from the menu item. But this expression doesn't work. How should a query like this be expressed?

Thanks ...

rsearle 10d ago

Further tests of Where statements:

{row:title} = "loop" produces error 2031: No data supplied for parameters in prepared statement, and the whole form fails, i.e. the form is not rendered

{data.quote:title} = "loop" produces an error message You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= "loop"' at line 1 in the message area of the form, i.e. the form does get rendered

"loop" = "loop" or 1 (i.e. always) are both accepted and produce a table list of all CF8 demo forms

'one ring to bind them all' LIKE '%loop%' and 'one loop to bind them all' LIKE '%loop%' also work; they produce an empty table and a complete list respectively 

... so the Where behavior kind of works, but the problem appears to be the syntax to access column values for evaluation. 

rsearle 1d ago

Finally figured it out. The form's WHERE value is just appended to the prepared SQL query, so column names are expressed the same way they would be in plain SQL, i.e. in back-ticks or just as literals. So if I pass a search string to the Table Listing demo form in a query parameter named search, this WHERE value works:

    `title` LIKE '%{data:search}%'

However, this variation employing the DB quote function does not work, it throws a SQL syntax error:

    `title` LIKE '%{data.quote:search}%'

But at least the WHERE behavior does work, provided that the column names are correct in SQL syntax, and the search argument is valid.

Max_admin 12h ago
Answer

You should not add the quotes when using the {data.quote:form_field}, but to get the "%" around the value you may need to set a variable using PHP:

$this->set("search", "%".$this->data("field")."%");

Then use {var.quote:search}

because if you use your current syntax you may get a SQL injection attack

Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
You need to login to be able to post a reply.