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>
Searching for "K*be*"
returns
Kaffeebecher and
Kugelschreiber
Searching for "K*be??er"
returns
Kaffeebecher
- Lay5DCKWUR.gif (118.24 KiB) Viewed 3091 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 3091 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.