Column Search

karlie

Hi, Can anybody tell me how I would go about using a button to instigate column search? I currently search on change, here's my code

// Category Search
$('#category').on( 'change', function () {
    table
        .columns( 5 )
        .search( this.value )
        .draw();
} );

Any help much appreiated!

rein

Hi! Please try this and let me know if it worked for you:

$("#button").on("click",function(){
    var searchVal =  $("#category").val();
    column.search(searchVal).draw();
 });

karlie

Many thanks, I tried your code, but how would I tell it to target column 5?

rein

Sorry, forgot about the column. Try table.columns( 5 ).search( searchVal ).draw();

karlie

Thanks very much for your help, it's appreciated. I managed to figure it out in the end, there was a problem with my code. I have implemented some filters to work with server side and that was at fault. They're working now although not perfectly. They are ordinary ajax/php dropdowns and datatables performs a column search based on the selection. Can see them here

karlie

Can anybody point me in the direction of more accurate searching in columns using server side? Currently it brings back results that aren't an exact match. I understand I need regex searching but are there any examples of code I would need to implement it?

colin

Hi Karlie,

This is a basic server-side example here. As it's a server-side script, you can implement it to do whatever you choose, it just needs to return the data in the format that DataTables is expecting.

Cheers,

Colin

allan

Just to add to what Colin said, the demo server-side processing script for DataTables uses a wildcard search - %searchTerm% to match in SQL. You can remove the % signs if you only want an exact match. You don't need regex for that with server-side processing.

Allan

karlie

Thanks Colin & Allan

karlie

Hi sorry to be a pain guys, but I'm just not getting this! I have the following dropdowns to search certain columns:
```

<

div class="col-3">
<?php

$connect = mysqli_connect("localhost", "db_user", "db_password", "db_name");
$country = '';
$query = "SELECT DISTINCT size FROM stones ORDER BY size ASC";
$result = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($result))
{
$size .= '<option value="'.$row["size"].'">'.$row["size"].'</option>';
}

<?php > Select Size <?php echo $size; ?> ``` Then this in the JS ``` // Size Search $('#size').on( 'change', function () { table .columns( 8 ) .search( this.value ) .draw(); } ); ``` I just can't see from any documentation how I can search for the exact match. If I search for 1.1mm it returns all 1.1mm results (good!) and also 11.1mm (bad!) ?>

If you have a moment to shed some light then that would be great, I think I'm just being incredibly slow.

allan

You are using server-side processing (from above - is that correct?). If so the search process is entirely up to yourself and it isn't documented. Only the client / server communication is documented to allow implementation on any server-side platform.

Our suggestion above is that you remove the wildcards from your SQL search term (again assuming you are using server-side processing).

Allan

karlie

I am using server side. The JS column search function is being passed a value which it's using to search the column, but it seems to me it isn't searching for an exact match. I don't think I'm using wildcards in my sql term. It is

$query = "SELECT DISTINCT size FROM stones ORDER BY size ASC";

which is then put into a select

$result = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($result))
{
 $size .= '<option value="'.$row["size"].'">'.$row["size"].'</option>';
}
<select name="size" id="size" class="action">
    <option value="">Select Size</option>
    <?php echo $size; ?>
   </select>

On change that select ID triggers the column search using the chosen select value

allan

No - that doesn't look like you are using wildcards, but it also doesn't look like a complex enough SQL statement to actually implement server-side processing. It also doesn't actually include any WHERE condition!

Can you link to the page showing the issue please?

Allan

karlie

http://stuartday.co.uk/dtgem/stonelist/index.php

If you click on Size Search and choose 1.1mm

allan

Thanks. What is the code inside table.stones.php? Are you using the Editor PHP libraries? If so you'd need to modify this line:

$query->where( $this->_ssp_field( $http, $i ), '%'.$search.'%', 'like' );

to be:

$query->where( $this->_ssp_field( $http, $i ), $search, 'like' );

to stop it from doing a wildcard search.

Allan

karlie

Wow, thanks a million, that worked, altering line 1543 in Editor.php. Would never have figured that out!