Guys, need some help or guidance. I have the example of filtering data with text input working https://wappler.io/docs/#Filtering-Database-Query-with-a-Text-Input and have used this several times, but now I need to filter on two columns of data. So basically a searchfield1 OR searchfield2. So instead of just filtering off ProductName, I can filter off ProductCode also. I have added another text input field and variable, then on my query I added another row and the condition is “OR”. That didn’t work, and I then I changed it to “AND”. That doesn’t work either. Has anybody done this, and if so, how?
I did this the other day.
on my page I have 2 text input fields (without a form)
<input type="text" class="form-control" id="filter_post_code" name="filter_post_code" placeholder="Filter: Post Code">
<input type="text" class="form-control" id="filter_email" name="filter_email" placeholder="Filter: Email">
Then I have a single Server Action with
Globals
$_GET (Right click > Add Variable) and name the variable exactly the same as the input id which is the same as the input name too
So mine looks like this
filter_post_code (Type > Text & Linked Field > BLANK)
filter_email (Type > Text & Linked Field > BLANK)
Then in Execute > Steps I have a Database Connection step and a Database Query that looks like this
SELECT * FROM AcConsumerDB WHERE acCdbAddressPostCode LIKE :&_GET_filter_post_code AND acCdbEmail LIKE :$_GET_filter_email
So basically in the columns area, I added my entire table, then in the conditions area I added the acCdbAddressPostCode field and set it to contains {{$_GET.filter_post_code}} AND acCdbEmail field set to contains {{$_GET.filter_email}}
Hope that helps, it is working perfectly
In my page I have 50k records and i add part of a UK post code like just RH and the records returned are about 900 and then I add part of the email address like sam and the records retuned come down to 4
I tend to use the underrated App Connect Data traversal in my projects, mainly because of the ease of creating client side filtering. As an added benefit, it keeps my serverside actions to a minimum.
Have a look at this image taken from https://www.dmxzone.com/go/32818/customers-dashboard-showcase where one search box accomodates either Company or Country.
@turn3636 that is how it is supposed to be set up in the query builder.
Can you post a link to your page where I can check what goes wrong?
@psweb I think he needs to use both search fields independently. In your example both must match a record, as you are using AND.
@psweb, I think that is exactly what I’m needing.I will try it out this morning.
@Teodor, That’s what I was initially thinking, just add another search field like the example. However when I use it that way, it does like what @psweb said, it has to match both search terms. I can’t show the site because of security, wish I could but here here is the debug info. Here you can see when I search on just one, I get this querystring:
but when I enter both (so both will match, the querystring is complete and it returns the row I want.
I want to match on either one, but not have to enter both. I would like to try the dashboard example, but it does not show the code. Do I just use the same variable for both filter columns in the query builder?
Thanks all for your help.
Please explain on your page do you wish to use two fields or just one?
Then in the query should BOTH be required and match or if ANY of them finds a match it should not require the other and filter the results?
I think I would rather use two search fields, to limit confusion. The query should return data if any of them finds a match.
you could use 2 queries then.
and hide the empty one
@turn3636, mine certainly works like you describe, I can enter data in a single input and it returns results without entering data in the second input, however if the data returned is still quite large you can choose to enter data into the second field which will further filter the already filtered data. Or if you prefer you could also only enter data into the second input and leave the first blank and it still works correctly.