[SOLVED] How to get values from a query checkbox field in autoactions

I set up a query checkbox field "cb_mycatids" which stores category IDs like 23|*|26|*|98 in the db.

In an autoaction type "query" I need to put these category IDs into a query like this:
Since the delimiter is |*| and not a comma I need to substitute this so I can use the values in the IN statement. How would I do this?

You should be able to just replace the |*| delimiter with a comma. Example as follows.


Next you need to use FIND_IN_SET instead of IN, because IN can't parse a string of comma separated values. Example as follows.


Information on FIND_IN_SET can be found below.

dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Thanks for the hint, spot on! That is what I could not solve.

I know it is not the purpose of this forum to teach us users SQL and PHP. But I was not even aware of that I can use PHP in that way in the query field.

Thank you very much. Outstanding support!

carin wrote:
Outstanding support!

Great Job Kyle!

(I am smelling a nice review coming on our JED Community Builder listing )

... a nice review coming ...

... is on its way, already done yesterday but I dont know if it will pass the JED editor with all the praising

I know it is not the purpose of this forum to teach us users SQL and PHP. But I was not even aware of that I can use PHP in that way in the query field.

You're actually using SQL in SQL. SQL has a pretty awesome set of functions that can mimic a lot of PHPs usage (even REGEX!). The below is a nice reference of what functions are likely available for most MYSQL installs.

dev.mysql.com/doc/refman/5.6/en/functions.html

The above gives a chapter like list so you can see functions relevant to strings only, dates only, etc.. the below is a full list of all the functions together.

dev.mysql.com/doc/refman/5.6/en/func-op-summary-ref.html

Also worth noting I've a Code Fieldtype plugin planned after CB 2.0 that outputs PHP as field value. It'll kinda function the same way the Code action does in CB Auto Actions. So that's something to look forward to if you're wanting to use PHP directly.