Search three DB columns with one search field

This is a bit tricky. You’ll have to make a custom filter. Try this:

Step 1
Install my Shortcodes, Actions and Filters plugin. We’re going to have to add some code.

Step 2
In that plugin, create a new code item.
– Name it “echo_var”
– check “shortcode”
– check “activated”
– keep “echo output” checked
– Add the following code and save.

if (isset($atts['var']) && isset($_REQUEST[$atts['var']])) {
    echo $_REQUEST[$atts['var']];
}

This is to pre-fill your form with values from the last time you clicked search.

Step 3
Create a second code item.
– Name is scuba_search
– do NOT check short code
– check “activated”
– Add the following code and save

require_once(ABSPATH . 'wp-content/plugins/contact-form-7-to-database-extension/CFDBPermittedFunctions.php');

function scuba_search($entry) {

    $basicFields = array('First-Name', 'Last-Name', 'Room-Number', 'Status');
    $bagFields = array('Bag-1', 'Bag-2', 'Bag-3');

    foreach ($basicFields as $field) {
        if (isset($_REQUEST[$field]) && $_REQUEST[$field]) {
            $pattern = "/.*{$_REQUEST[$field]}.*/i";
            if (!preg_match($pattern, $entry[$field])) {
                return false;
            }
        }
    }

    if (isset($_REQUEST['Bag']) && $_REQUEST['Bag']) {
        $bag = trim(strtolower($_REQUEST['Bag']));
        $flag = false;
        foreach ($bagFields as $field) {
            if (trim(strtolower($entry[$field])) == $bag) {
                $flag = true;
                break;
            }
        }
        if (!$flag) {
            return false;
        }
    }

    return true;
}

cfdb_register_function('scuba_search');

This is your custom filter code.

Step 4
Change your form to this

<form action="" method="POST">
    <fieldset>
        <legend>Search by Name or Room Number</legend>
        <label>First Name</label><input name="First-Name" type="text" id="search-first-name" value="[echo_var var='First-Name']"/>
        <label>Last Name</label><input name="Last-Name" type="text" id="search-last-name" value="[echo_var var='Last-Name']"/>
        <label>Room Number</label><input name="Room-Number" type="number" id="search-room-number" value="[echo_var var='Room-Number']"/>
    </fieldset>
    <fieldset>
        <legend>Search by Bag Number</legend>
        <label>Bag</label><input name="Bag" type="number" id="search-bag" value="[echo_var var='Bag']/>
        <label>Out</label><input class="radio-button" type="radio" name="Status" value="Out" checked="checked"/>
        <label>In</label><input class="radio-button" type="radio" name="Status" value="In"/>
    </fieldset>
    <input value="Search" type="submit" id="search-submit-button"/>
</form>

Step 5
Change your shortcode to:

[cfdb-datatable form="Equipment Rental" role="Editor" edit="cells" show="First-Name,Last-Name,Room-Number,Departure-Date,Full-Sets,Masks,Snorkels,Fins,Cameras,Optical-Masks,Bag-1,Bag-2,Bag-3,Status" filter="scuba_search()"]

Hi Michael

Thanks ever so much for your help. The search logic works perfectly.
There are still a couple of issues though.

1. When the search page loads, the following fields are pre-filled:

“First Name” is pre-filled with value: “[echo_var var=’First-Name’]”
“Last Name” is pre-filled with value: “[echo_var var=’Last-Name’]”
“Bag” is pre-filled with value: “[echo_var var=’Bag’]”

If I run a search with any of these values inputted then an error message appears for each record in the DB. I need to first delete these values from each field before I can successfully perform a search.

For now I have managed to work around this problem by using javascript to clear the form fields on page load.

<script>
var input = document.getElementById('search-first-name');
input.value = '';
</script>
<script>
var input = document.getElementById('search-last-name');
input.value = '';
</script>
<script>
var input = document.getElementById('search-bag');
input.value = '';
</script>

I’m sure that there is a more elegant solution ??
As I’ve said before my coding ability is very limited.

2. Each time the search page loads a search is automatically run on the DB and all of the DB entries are automatically displayed by the CFDB datatable shortcode.

If possible I would prefer if no data is displayed until after a search is performed by a user.

Thanks for the time and effort you’ve taken to help me.
I greatly appreciate it.

Tom

Right…here are some tweaks to get it working right.

Form Definition

<form action="" method="POST" id="search-form">
    <fieldset>
        <legend>Search by Name or Room Number</legend>
        <label>First Name</label><input name="First-Name" type="text" id="search-first-name" [echo_var var='First-Name']/>
        <label>Last Name</label><input name="Last-Name" type="text" id="search-last-name" [echo_var var='Last-Name']/>
        <label>Room Number</label><input name="Room-Number" type="number" id="search-room-number" [echo_var var='Room-Number']/>
    </fieldset>
    <fieldset>
        <legend>Search by Bag Number</legend>
        <label>Bag</label><input name="Bag" type="number" id="search-bag" [echo_var var='Bag']/>
        <label>Out</label><input class="checkbox-button" type="checkbox" name="Status[]" value="Out" [echo_var var='Status' checked="Out"]/>
        <label>In</label> <input class="checkbox-button" type="checkbox" name="Status[]" value="In" [echo_var var='Status' checked="In"]/>
    </fieldset>
    <input type="hidden" name="x" value="1"/>
    <button type="submit" id="search-submit-button">Search</button>
    <button type="button" id="clear-button" onclick="jQuery('#search-form input').val('').removeAttr('checked'); ">Clear</button>
</form>

Shortcode

[cfdb-datatable form="Equipment Rental" role="Editor" edit="cells" show="First-Name,Last-Name,Room-Number,Departure-Date,Full-Sets,Masks,Snorkels,Fins,Cameras,Optical-Masks,Bag-1,Bag-2,Bag-3,Status" filter="intval(1)=$_POST(x)&&scuba_search()"]

Update code or echo_var shortcode

if (isset($atts['var']) && isset($_REQUEST[$atts['var']])) {
    if (isset($atts['checked']) && is_array($_REQUEST[$atts['var']]) &&
            in_array($atts['checked'], $_REQUEST[$atts['var']])
    ) {
        echo 'checked';
    } else {
        echo "value=\"{$_REQUEST[$atts['var']]}\"";
    }
}

Update code for search function

require_once(ABSPATH . 'wp-content/plugins/contact-form-7-to-database-extension/CFDBPermittedFunctions.php');

function scuba_search($entry) {

    $basicFields = array('First-Name', 'Last-Name', 'Room-Number');
    $checkboxFields = array('Status');
    $bagFields = array('Bag-1', 'Bag-2', 'Bag-3');

    foreach ($basicFields as $field) {
        if (isset($_REQUEST[$field]) && $_REQUEST[$field]) {
            $pattern = "/.*{$_REQUEST[$field]}.*/i";
            if (!preg_match($pattern, $entry[$field])) {
                return false;
            }
        }
    }

    foreach ($checkboxFields as $field) {
        if (isset($_REQUEST[$field]) && is_array($_REQUEST[$field])) {
            if (! in_array($entry[$field], $_REQUEST[$field])) {
                return false;
            }
        }
    }

    if (isset($_REQUEST['Bag']) && $_REQUEST['Bag']) {
        $bag = trim(strtolower($_REQUEST['Bag']));
        $flag = false;
        foreach ($bagFields as $field) {
            if (trim(strtolower($entry[$field])) == $bag) {
                $flag = true;
                break;
            }
        }
        if (!$flag) {
            return false;
        }
    }

    return true;
}

cfdb_register_function('scuba_search');

Sorry, hopefully last tweaks. Shortcodes are same as in my last post but:

Form:

<form action="" method="POST" id="search-form">
    <fieldset>
        <legend>Search by Name or Room Number</legend>
        <label>First Name</label><input name="First-Name" type="text" id="search-first-name" [echo_var var='First-Name']/>
        <label>Last Name</label><input name="Last-Name" type="text" id="search-last-name" [echo_var var='Last-Name']/>
        <label>Room Number</label><input name="Room-Number" type="number" id="search-room-number" [echo_var var='Room-Number']/>
    </fieldset>
    <fieldset>
        <legend>Search by Bag Number</legend>
        <label>Bag</label><input name="Bag" type="number" id="search-bag" [echo_var var='Bag']/>
        <label>Out</label><input class="checkbox-button" type="checkbox" name="Status[]" value="Out" [echo_var var='Status' checked="Out"]/>
        <label>In</label> <input class="checkbox-button" type="checkbox" name="Status[]" value="In" [echo_var var='Status' checked="In"]/>
    </fieldset>
    <input type="hidden" name="x" value="1"/>
    <button type="submit" id="search-submit-button">Search</button>
    <button type="button" id="clear-button" onclick="jQuery('#search-form input[name!=\'x\']').val('').removeAttr('checked'); ">Clear</button>
</form>

Search

require_once(ABSPATH . 'wp-content/plugins/contact-form-7-to-database-extension/CFDBPermittedFunctions.php');

function scuba_search($entry) {

    $basicFields = array('First-Name', 'Last-Name', 'Room-Number');
    $checkboxFields = array('Status');
    $bagFields = array('Bag-1', 'Bag-2', 'Bag-3');

    foreach ($basicFields as $field) {
        if (isset($_REQUEST[$field]) && $_REQUEST[$field]) {
            $pattern = "/.*{$_REQUEST[$field]}.*/i";
            if (!preg_match($pattern, $entry[$field])) {
                return false;
            }
        }
    }

    foreach ($checkboxFields as $field) {
        if (isset($_REQUEST[$field]) && is_array($_REQUEST[$field])) {
            $needle = strtolower(trim($entry[$field]));
            $haystack = array_map('trim', $_REQUEST[$field]);
            $haystack = array_map('strtolower', $haystack);
            if (!in_array($needle, $haystack)) {
                return false;
            }
        }
    }

    if (isset($_REQUEST['Bag']) && $_REQUEST['Bag']) {
        $bag = trim(strtolower($_REQUEST['Bag']));
        $flag = false;
        foreach ($bagFields as $field) {
            if (trim(strtolower($entry[$field])) == $bag) {
                $flag = true;
                break;
            }
        }
        if (!$flag) {
            return false;
        }
    }

    return true;
}

cfdb_register_function('scuba_search');

Michael you’re a genius!
You’ve made my life a lot easier ??
Much thanks from myself and all my staff at the dive centre.
Best regards
Tom