I am using DataTables and have converted an existing client-side to server-side (Java, MySQL). I have one last issue which is with the search functionality. With the client-side if I enter "Glyn Bartlett 20/12/2000" then I get all instances of this string (i.e., First Name: Glyn, Surname: Bartlett, DOB: 20/12/2000) and on each key-up the list is progressively filtered (i.e., all rows with a "g" are displayed then all rows with a "gl", etc). I have followed the examples which use OR statements to compare the string for each column that can be searched. As a result after the first space (e.g., "Glyn ") this no longer works.
I then found MySQL Full-Text Searches. However, that will only match on a full word (e.g., if "glyn" is entered then no rows are returned until the full word is entered - I suppose that is why they call it "Full-Text").
What is the most efficient way to implement this search in server-side processing considering:
I have eight columns that can be searched
Each key-up triggers the server-side call
I want to progressively filter on each key-up
I want it to work on dates entered as dd/mm/yyyy and stored as yyyy-mm-dd
Yes, DataTables sends the search request on each key-up in the "Search:" field.
"LIKE 'glyn%' will not work when searching for "glyn bartlett".
I have now tried the multiple OR statements (one for each column) with searchValue = searchValue.replace(" ", "%");. However, that works until "glyn " and then stops at "glyn b". This also needs to work if someone enters "bartlett glyn". These all work on the client-side version where the search is handled by DataTables. The issue is that I need to be able to replicate how DataTables does that search with my java code and/or MySQL.
I am concatenating my stored procedure depending on the values received. The two ways I have tried are:
This works on "g", "gl", "gly", "glyn", "glyn " and stops working on "glyn b".
This does not work until "glyn" is entered.
Note: I need the OR MATCH in the second code because "sd_start_date" is in a different table.