search box , queries

[fdissait]

Hello,
do you plan to allow the use of wildcards *, ?, % in quick searcg box, and other filter queries ?
Or is it a hook way to perform it ?

Thanks, keep away from Covid
François

[jsetzer]

By default, MySQL database (and most others) use different characters than * and ? as wildcards:

• % (percent)
  for any number of characters
• _ (underscore)
  for exactly one character

Check it out, it works in quicksearch by default.

Anyway, users are used to using "*" and "?" instead, which is more windows-like.

In a project for a customer I did some hooks-only replacement of "*" by "%" and "?" by "_" just before execution of search. In that project, I only did it for a certain tableview, because there were specific requirements for search. So I did those changes in TABLENAME-tv.js. But if you want to enable that wildcard-replacement globally, you can also write the following script in hooks/header-extras.php:

Code: Select all

<!-- file: hooks/header-extras.php -->
<script>
    const wildcard_search = true; // toggle this for testing purposes 
    jQuery(document).ready(function() {
        if (wildcard_search) {
            resolveSearchString(false);
            $j('form[name=myform]').on('submit', function() {
                // $j('#SearchString').parent().addClass("hidden");
                resolveSearchString();
            });
        }
    });
    function resolveSearchString(on = true) {
        const e = $j('#SearchString');
        let val = e.val();
        if (on) {
            if (!val.length || val.search('%') >= 0) return;
            e.val(val.replaceAll('*', '%').replaceAll('?', '_'));
        } else {
            e.val(val.replaceAll('%', '*').replaceAll('_', '?'));
        }
    }
</script>

Result

Searching for "K*be*"
returns Kaffeebecher and Kugelschreiber

Searching for "K*be??er"
returns Kaffeebecher

Lay5DCKWUR.gif (118.24 KiB) Viewed 3077 times

Small backdraw and workaround

On search, for a millisecond you can see the charater replacement and perhaps red underlined fragments due to spelling correction of your browser. If you, like me, dislike this, you can hide the searchbox immediately before replacement. Just uncomment the following line and it will look much better from my personal opinion.

Code: Select all

// $j('#SearchString').parent().addClass("hidden");

Result with hiding searchbox:

W1IVdnxH4s.gif (18.36 KiB) Viewed 3077 times

Hope this helps someone and you like this hooks-only enhancement. Any feedback is welcome!

PS: On that specific customer project the resolveSearchString() function is much more elaborated. For example, if search contains a slash like in "3/117", I'm building a custom search pattern with leading zeroes like "00003/0000000117". This makes it easier for users to quickly search for records if there are naming conventions or defined patterns.

[fdissait]

Many thanks Jan,
I knew the Mysql features, had 'nt the idea to use it in Quick Search !!!!
François