Forums

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

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. 

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.