Column Search

Column Search

karliekarlie Posts: 83Questions: 17Answers: 0

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!

This question has an accepted answers - jump to answer

Answers

  • reinrein Posts: 12Questions: 3Answers: 0
    edited March 2018

    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();
     });
    
  • karliekarlie Posts: 83Questions: 17Answers: 0
    edited March 2018

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

  • reinrein Posts: 12Questions: 3Answers: 0

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

  • karliekarlie Posts: 83Questions: 17Answers: 0

    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

  • karliekarlie Posts: 83Questions: 17Answers: 0

    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?

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • karliekarlie Posts: 83Questions: 17Answers: 0

    Thanks Colin & Allan

  • karliekarlie Posts: 83Questions: 17Answers: 0
    edited March 2018

    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>';
    }
    ?>
      <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
       <select name="size" id="size" class="action">
        <option value="">Select Size</option>
        <?php echo $size; ?>
       </select>
            </div>
            
        </div>
    

    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.

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • karliekarlie Posts: 83Questions: 17Answers: 0

    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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • karliekarlie Posts: 83Questions: 17Answers: 0

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

    If you click on Size Search and choose 1.1mm

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin
    Answer ✓

    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

  • karliekarlie Posts: 83Questions: 17Answers: 0

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

This discussion has been closed.