1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. [SOLVED] Mysql query to locate users with specific Query Check Box (Multiple) selected

[SOLVED] Mysql query to locate users with specific Query Check Box (Multiple) selected

7 years 1 month ago - 7 years 1 month ago #293284 by cpaschen
[SOLVED] Mysql query to locate users with specific Query Check Box (Multiple) selected was created by cpaschen
I have a CB field of type Query Check Box (Multiple) in use.

When users select a few checkboxes, the field data ends up with the IDs delimited by "|*|".

I need to be able to query this data using mysql directly (and can't do it within a PHP code block ... I need it to actually be part of a mysql view that I create).

I know that if this were a comma-delimited list, I could just use WHERE IN(field); however, because CB uses a 'unique' delimiter, the WHERE IN syntax doesn't work.

Although this isn't a specific CB question, I've not run across any means (other than complicated user defined functions) to perform this type of a query.

Do you have some 'secret' solution for building queries like this on the multiple check box query field type?

Please Log in to join the conversation.

7 years 1 month ago #293288 by krileon
Replied by krileon on topic Mysql query to locate users with specific Query Check Box (Multiple) selected
Use LIKE with wildcards is the simplest approach, but your values can not contain another value. Example as follows.

`cb_multicheckbox` LIKE '%VALUE%'

Now if you've VALUE|*|VALUE1 then the above will match both. Another approach is to use FIND_IN_SET and convert the delimiter. Example as follows.

FIND_IN_SET( 'VALUE', REPLACE( `cb_multicheckbox`, '|*|', ',' ) ) > 0

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

7 years 1 month ago #293315 by cpaschen
Replied by cpaschen on topic Mysql query to locate users with specific Query Check Box (Multiple) selected
The FIND_IN_SET with the converted delimiter option was the PERFECT solution. That little snippet made the case work AND gives me all sorts of ideas for future applications.
THANKS!!!!
The following user(s) said Thank You: krileon

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. [SOLVED] Mysql query to locate users with specific Query Check Box (Multiple) selected
Time to create page: 0.196 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Developer Members Support
  5. [SOLVED] Mysql query to locate users with specific Query Check Box (Multiple) selected

Search

Login / Logout

User Menu