What is the most efficient way to search across multiple database columns?

[Glyndwr Bartlett]

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
Kind regards,

Glyn

[Carey Brown]

SELECT ... WHERE name LIKE 'glyn%'

[Carey Brown]

Are you expecting the server to return a new result set with each character that you enter?

[Glyndwr Bartlett]

Hi Carey,

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:

String selectQry2c = " AND (at_cub_details.cd_surname LIKE ? OR at_cub_details.cd_first_name LIKE ? " +
	  			" OR at_cub_details.cd_dob LIKE ? OR at_cub_details.cd_gender LIKE ? " +
	  			" OR at_cub_details.cd_scout_no LIKE ? OR at_section_details.sd_start_date LIKE ? " +
	  			" OR at_cub_details.cd_invested LIKE ? OR cd_birthday_notify LIKE ?) ";

searchValue = searchValue.replace(" ", "%");
ps.setString(1, "%" + searchValue + "%"); //same for 1 to 8

This works on "g", "gl", "gly", "glyn", "glyn " and stops working on "glyn b".

and:

String selectQry2c = " AND (MATCH(cd_surname, cd_first_name, cd_dob, cd_gender, cd_scout_no, " +
  				" cd_invested, cd_birthday_notify) AGAINST(? IN BOOLEAN MODE)"
  				+ " OR MATCH(sd_start_date) AGAINST(? IN BOOLEAN MODE)) ";

ps.setString(1, searchValue); //same for 1 to 2

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.

Kind regards,

Glyn

[Carey Brown]

According to your code you are searching for '%glyn%b%'. The percent only works on the ends, not in the middle. Why aren't you searching for firsname and surname on two separate columns?

WHERE firstname LIKE 'glyn%' AND surname LIKE 'b%'

[Glyndwr Bartlett]

Hi Carey,

Because in my current DataTables there is one "Search:" field, not one for each column. I think there is a way to implement search on each column so I will investigate that.

Thank you so much for your help and asking the right questions :-)

Kind regards,

Glyn