DataTables Search "Male" and Female"

Question

I am using datatables and it has a built-in search (if that's what you call it), now I'm having problem on searchin gender on my table list because whenever I search for "Male" , both male and female shows up in the list. What will I do so it will filter only the gender "Male" if I search for Male. But Female if I search for Female? I'm sorry if i haven't tried anything because I really don't have an idea. I tried searching though, but those with same problem with mine don't have answer that I understood, so I am just trying that maybe you guys can help me. If you can't because I haven't tried anything, I understand. But I'm still hoping. thank you in advance!

Database Name - db_seq

Table name - Profile

Table columns - name, gender, username, password

EDIT: My code

<?php include('dbcontroller.php');
    $sql = "SELECT name, gender FROM profile ORDER by name ASC";
    $res = mysqli_query($conn,$sql)or die(mysqli_error());
    ?>
    <table id="batchList" class="table table-striped table-bordered" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Gender</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Gender</th>
            </tr>
        </tfoot>        
        <tbody> 
        <?php
        while ($row = mysqli_fetch_array($res)) {
            $name = $row['name'];
            $gender = $row['gender'];
        ?>
            <tr>
                <td><?php echo $name;?></td>
                <td><?php echo $gender;?></td>                  
            </tr>
<?php
    }
?>
    </tbody>
</table>
<?php
mysqli_close($conn);
?>


<script>
$(document).ready(function() {
    $('#batchList').DataTable();
} );
</script>

Do you understand that "male" are the last four letters of "female", and so it depends on _how exactly_ your search code works? So please show that code. — CodeCaster, May 07 '16 at 09:31
Yes I do understand that. I can't give you the part where the searching happens because it is a jquery from datatables and it is not-so-understandable (Atleast for me) hope you undertstood my point @CodeCaster — Louie, May 07 '16 at 09:38
If you can't show your code, nobody can answer this. For this column, you simply want an exact match, not a "LIKE" query. More than that can't be said without any code. — CodeCaster, May 07 '16 at 09:38
I know. Thank you tho. But my databales.min.js is from this site > (https://cdn.datatables.net/1.10.11/js/jquery.dataTables.min.js) @CodeCaster — Louie, May 07 '16 at 09:45
We don't need to see the dataTables.js, we need to see your code. — CodeCaster, May 07 '16 at 09:46
I didn't have in my code like the searching from my database because when you use datatables, searching is already part there and i have no idea how they did that but they're genius. except that in my case, i had a problem searching with the same word with another. — Louie, May 07 '16 at 09:48
If it's just `$("#yourTable").DataTable()` then that's fine too. Anyway it [looks like you should simply disable smart searching on the relevant column or apply an anchored regex filter](https://www.datatables.net/forums/discussion/4096/filtering-an-exact-match). See also [jQuery DataTables - Filter column by exact match](http://stackoverflow.com/questions/8609577/jquery-datatables-filter-column-by-exact-match). — CodeCaster, May 07 '16 at 09:50
Thanks for the links!!! I will try whatever it is written there. Thank you @CodeCaster — Louie, May 07 '16 at 09:55
You have an error. [`mysqli_error()`](https://www.php.net/manual/en/mysqli.error.php) needs one argument. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Mar 06 '21 at 20:25

score 0 · Answer 1 · edited Mar 06 '21 at 20:25

The examples below may be useful if you are using the ssp class.

ssp.class.php -> filter method

// Individual column filtering
    if ( isset( $request['columns'] ) ) {
        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];

            $str = $requestColumn['search']['value'];

            if ( $requestColumn['searchable'] == 'true' &&
             $str != '' ) {
                if(!empty($column['db'])){
                    if ($requestColumn['search']['regex'] == 'true') {
                        $binding = self::bind( $bindings, $str, PDO::PARAM_STR );
                        $columnSearch[] = "`".$column['db']."` = ".$binding;
                    } else {
                        $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                        $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
                    }
                }
            }
        }
    }

javascript DataTable's configuration object

initComplete: function () {

                this.api().columns().every( function () {
                    var that = this;
                    var column = this;

                    $( 'input', this.footer() ).on( 'keyup change clear', function () {
                        if ( that.search() !== this.value ) {
                            that
                                .search( this.value )
                                .draw();
                        }
                    } );

                    $( 'select', this.footer() ).on( 'change clear', function () {

                        if ( that.search() !== this.value ) {
                            if (this.value === '') {
                                that
                                    .search( this.value )
                                    .draw();
                            } else {
                                that
                                    .search( this.value, true, false)
                                    .draw();
                            }
                        }
                    } );

                } );
            },

DataTables Search "Male" and Female"

1 Answers1