Win a copy of Zero to AI - A non-technical, hype-free guide to prospering in the AI era this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

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

 
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 7381
66
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you expecting the server to return a new result set with each character that you enter?
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



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

and:



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
Saloon Keeper
Posts: 7381
66
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
No thanks. We have all the government we need. This tiny ad would like you to leave now:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic