Filtering data with multiple checkboxes

This is an excellent tutorial but I have one small issue. The database (I’ve inherited it from a Wordpress site) has the array stored with the pipe ( | ) separator, eg. Single|Double|Parking. The checkboxes are dynamic (I’ve got those working fine) but I think the issue is because the array is being sent comma-separated but the database is pipe-separated.

How can I fix this?

What is the issue you are having? Filtering the records by selected checkboxes, or storing checkboxes values into the db?

Filtering the records is the issue.

What if you first join the values using your character | and then split them again:

{{$_GET.filter.join("|", "").split("|")}}
2 Likes

I’m afraid that didn’t work. This is meant to go in the conditions tab of the query in the Server Connect action, yes?

Yes, in the value field. But make sure to replace the get variable name with your own.

Yep, I changed the variable name and have tried these combinations…

{{$_GET.filter_amenities.join("|", "".split("|")}}

{{$_GET.filter_amenities.join("|", "".split(",")}}

{{$_GET.filter_amenities.join(",", "".split("|")}}

but none of them work.

I’ll test this and will get back to you later.

1 Like

Actually you don’t need to change the value returned by the checkboxes, it’s in a right format and just splitting it is enough.

The issue is with the DB value, which is not in the required type/format. You store the values in a single field as a pipe separated string, while our example here shows that they are stored separately in separate rows, which returns them as an array.
You will either have to use some custom query for this, or use a separate relational table to store the values in separate rows.

Thanks @Teodor. I’ve been thinking about this and the easiest solution is to simply do a search and replace on the database and change all the pipes to commas. I can do that easily and there aren’t many scripts I’ll need to change to get it all working again. I’ve been trying to build the new site using the same data structure as the existing Wordpress site but when the site launches, I’ll only need to import everything once so doing the search and replace then won’t be a problem.

Thanks again.

1 Like

I’m having real trouble with this. In the dev console I can see the GET variables being set correctly:

querySearchResults.php?filter_min=&filter_max=&filter_airports=&filter_amenities=Parking%2CSingle

(I know that %2C is a comma so fine there - in fact, with only one checkbox ticked it still doesn’t work)

The condition is correct following this tutorial. I’ve tried to us IN a few times and never actually had it working.

Could it be a bug or is there something else I need to check? I’ve redone it all several times following this tutorial but still no joy.

But then what data are you checking exactly? What is the database value being checked if it is “IN” the selected checkboxes?

Your expressions are wrong you are missing a closing bracket just before .split

The database value has this:

Single,Internet,Parking

My checkboxes have the various options so, logically, it should work.

I just want to filter the output for any checkboxes that are ticked, as per the tutorial.

That’s the problem - logically it won’t work with data stored like that.
You are comparing the string Single,Internet,Parking stored in your db field with an array of values, returned by the checkboxes. So:
Single,Internet,Parking IN ['Internet', 'Parking'] won’t return anything.

You just need to store the amenities in a relational table, which stores the record ID and the amenity associated with it, just as:

product1 - amenity1
prodcut1 - amenity2
product1 - amenity3
product2 - amenity2
...
product99 - amenity1

But isn’t that why split is used? I thought that turned the value of:

Single,Internet,Parking

into an array of:

Single
Internet
Parking

I’ve found numerous tutorials which are using a single field to store comma-separated values.

The split splits the value returned by the checkbox(array), not your database field value :slight_smile:

Maybe check Brian’s tutorial about relations:

1 Like

Hmm, thanks for the clarification.

I have a good understanding of relational databases and would normally use many-to-many tables for all things like this. However, because I’m using an existing database structure, I was trying to keep that structure. However, I shall write a script to convert the data from comma-separated fields to relational tables so it’s a better end product.

2 Likes