Query Question: If $_Get is in field

[brad]

I have a situation where I need the dynamic GET value to be listed first. Right now the GET variable is always 4 digits. The noc1, noc2 and noc3 values can be 1-4 digits. I need to get a list of records that the GET variable contains one of the three values.

For example:

{{$_GET.noc}} CONTAINS c_clients.client_noc1

Is this possible somehow?

I tried this in a custom query but it has Syntax errors of course.

SELECT * FROM c_clients
WHERE client_employ = 'Looking For Work'
AND (:P1 /* {{$_GET.noc}} */ CONTAINS client_noc1
OR :P1 /* {{$_GET.noc}} */ CONTAINS client_noc2
OR :P1 /* {{$_GET.noc}} */ CONTAINS client_noc3)

[bpj]

It’s quite confusing from your description

Is it that you could have a GET 1578
And a noc 259
And look to see if any of the digits match? In this case 5?

[brad]

The GET could be 1411 for example.

So I would need to find all records where the GET (1411) contains ‘1’, '‘4’, ‘14’ ‘141’ or ‘1411’

[bpj]

I reckon you could do this with a custom formatter to convert the GET value in to an array of possible values and then use an IN query on noc1 for that array. What server mode are you using?

[brad]

PHP/MySQL

[bpj]

Something like this should work as a formatter in a set value step:

function formatter_nocsplit($val){
for ($i = 0; $i < strlen($val); $i++){
$arr[] = substr($val,0,$i+1);
}
return $arr;
}

setValue
nocsplitvalue with a value $_GET.noc.nocsplit()

Then in your query conditions do
noc1 IN {{nocsplitvalue}}
OR noc2 IN {{nocsplitvalue}}
OR noc3 IN {{nocsplitvalue}}

[bpj]

just curious… how did you get on with this?

[brad]

Still battling with it. Not sure how/where to implement your solution yet. Working on it.

Thank you again.

[bpj]

This might help:

Creating Custom Formatters (PHP and client-side)

I have been trying to supplement the native formatters in Wappler and found that most things can be added by using custom formatters. What I found, though was the technique for this was hidden in answers to specific questions so thought a short tutorial may help some: Server-side (php) In dmxConnectLib/lib/formatters create a file, call it something like customformatters.php (do not alter the native files as it could be overwritten with updates) Add the relevant code: the function name shoul…

Basically you would use the formatter in my previous post to create an array of the $_GET.noc options using Set Value named nocsplitvalue:

if $_GET.noc was 1763
$_GET.noc.nocsplit() you would give you an array of [1,17,176,1763]

In your database query, you can use this array with an IN operator for each of noc1, noc2, noc3
i.e. WHERE noc1 IN nocsplitvalue OR noc2 IN nocsplitvalue OR noc3 IN nocsplitvalue

[brad]

Thanks Ben,

That would work perfectly for our use case. I will check that doc and see if I can figure it out.

Much appreciated.