Filtering Database Query with Multiple Checkboxes

@mrbdrm @Teodor

Well I spoke to soon, on a page that is similar to @Teodor 's original posts above. The problem I’m having is that there are two conditions in the Query Builder. The first one narrows the list of names by a group and is passed in as a {{$_GET.chapter_id}} when the page loads. Then the second condition is the one passed in from the form for the checkboxes, the “category” from Teodor’s example. The issue is that when the page loads for the first time, I get back all of the names from the system instead of just the ones for the passed group. It’s like the “category” is empty so it craps out on all of the $_GET values passed in.

did you remember to bind the variabe to the query connection?

@Hyperbytes, Yes, all binded.

It appears that when the page loads it is ignoring the “WHERE” portion of the SQL and bringing back all of the users. If you check both check boxes, the correct users are displayed. If both boxes are un-checked then you get the full list of users.

Is there a way to set the initial value of the array when the page loads so that the query is executed correctly?

use add group

Yes. @AdrianoLuiz is correct
You can use groups like this

7 posts were split to a new topic: Filtering data with a checkbox

11 posts were split to a new topic: Filtering data with a checkboxes

Hi,
Same issue for me. My query contains several tables and there are several conditions in addition to the filter condition. Any idea please ?

The part you want to pay attention to is here:

That should help you.

The example works perfectly on a numeric type field. In my case, the field type is SET and it works if the record contains only one value. Thank you in advance for your help.

Hi everyone, any idea to solve my issue ?

Any chance you could try give way more information and screenshots so I can try get a better idea of which filters are and are not working.

Hi psweb,
Thanks for your reply.
The screenshot below is the representation of my table which has 2 fields:

  • id: type smallint, ai
  • Casques audios connecteurs: set type, the values are: Non applicable,Prise 2.5 mm,USB-C,1, Prise 6.35 mm, 1

12 records have been entered as you can see.


I want to filter the “Casques audios connecteurs” column. By using “IN” in the conditions, it displays only the records which have only one value. It ignores all records that contain more than one value in the “Headphones connector” field even if the value that is checked is one of them.


With “CONTAINS”, it displays all the records which contain the selected value.
3

But as soon as you check a 2nd value, it displays only the records which contain these 2 values and ignores the records which contain only one of these 2 values. And so on.
3bis
3bis2

Ok, makes sense so far, last thing, could you paste the code used in your checkbox group here, then I will have enough info to test on my end. I have only ever used the IN operator with similar data, in other words 1,2,3,4,5,6 etc, so just wondering how yours works with the SET of values, but will test it out when i see your checkbox group code.

1 Like

I had a similar scenario and took the decision to switch to a many-to-many table for the values of the checkboxes as that is much easier to work with and more flexible. Would you be able to make the same change?

The table structure would be something like…

RecordID (int, index, ai)
ProductID (int)
FeatureID (int)

Then you can easily make the searches and other functions.

I cant make it work like that either, @sitestreet is right, best to change to a different database solution, then it’s easy.

I have too many tables and it will take me a long time if I have to take this new approach. Thank you anyway for your effort, I will continue to check on my side.

OK, but I was in the same position and got a colleague to write a script to convert all the fields which contained the piped data into separate records in the many-to-many so it wasn’t too big a job.

Ok, keep me posted for the script, I will see on my side if it is to execute it in my case, thx again