I have a table which features 37 columns, of various types, INTs and VARCHARS and 2 LONGTEXT columns. The client wants a search to search the table and find the rows that match.
However, I'm having trouble with this. Here is what I've done so far:
1) My initial view was to do a massive set of OR queries - however I was put off this by the fact I would need to supply the search data ~30 times, which is massive repetition and I'm sure there;s a better way than this.
SELECT MemberId, MemNameTitle, MemSName, MemFName, MemPostcode, MemEmail FROM MemberData WHERE MemFName LIKE CONCAT('%',?,'%') OR MemSName LIKE CONCAT('%',?,'%') OR MemAddr LIKE CONCAT('%',?,'%') OR MemPostcode LIKE CONCAT('%',?,'%') OR MemEmail LIKE CONCAT('%',?,'%') ...etc...
Etc. Etc. That's a massive set of OR's and really unwieldy.
2) I thought I'd try and rework it to place all the columns in brackets and then only ask the query once, I saw a similar piece of code on SO but not sure that was correctly working, but it was an insprition, at least:
SELECT MemberId, MemNameTitle, MemSName, MemFName, MemPostcode, MemEmail FROM MemberData WHERE (MemNameTitle OR MemFName OR MemSName OR MemAddr OR MemPostcode OR MemEmail OR MemSkype OR MemLinkedIn OR MemFacebook OR MemEmailTwo ...etc...) LIKE CONCAT('%',?,'%') GROUP BY MemberId
This code executes without apparent error but fails as it always returns no result, as in 0 fields returned. I can't see why, from an initial view,
3) So, with some research on OS I found a rearrangement using the IN keyword, but from previous questions on here Is it possible to use LIKE and IN for a WHERE statment? it appeared not to work.
What I wanted to get was something like:
SELECT MemberId, MemNameTitle, MemSName, MemFName, MemPostcode, MemEmail FROM MemberData WHERE MemNameTitle, MemFName, MemSName, MemAddr, MemPostcode, MemEmail, MemSkype, MemLinkedIn, ...etc ... MemFax, MemberStatus, CommitteeNotes, SecondAddr, SecondAddrPostcode IN (LIKE CONCAT('%',?,'%') )
This is crudy syntax but I hope you get the idea I want to get, I want to search many fields for the same value using a LIKE % % clause. Fields are variously TEXT/VARCHAR types.
4) I then looked into MySQL full text searches but this quickly became useless as this is only applied to TEXT type rather than VARCHAR type searching. I considered before each search changing each VARCHAR column to a TEXT column but figured that was also be relatively processor intensive and seemed illogical for a search that many people must want to do?
So, I'm out of ideas..... Can you help me search this way? Or suggest why my code in attempt 2 always returns Zero rows?
5) I have been looking at rearranging the IN clause statement and came up with this:
SELECT *(lazy typing!) WHERE CONCAT('%',?,'%') IN (MemNameTitle, MemFName, MemSName, MemAddr, MemPostcode, MemEmail, MemSkype, ...etc... CommitteeNotes, SecondAddr, SecondAddrPostcode) GROUP BY MemberId
However this returns a result, but the result is always the last row of the table. This doesn't work.
From Ravinder, using CONCAT_WS for all the fields - this works in my case, although something in my mind does find CONCATs somewhat ugly, but oh well.
SELECT * FROM MemberData WHERE CONCAT_WS('<*!*>', MemNameTitle, MemFName, MemSName, MemAddr, MemPostcode, MemEmail, MemSkype, MemLinkedIn, ...etc... MemberStatus, CommitteeNotes, SecondAddr, SecondAddrPostcode) LIKE CONCAT('%',?,'%') GROUP BY MemberId ";
The table will eventually have a few thousand rows, and I am a little worried that as this query will concat 24 columns for each row on the table for each search, that this could easily become quite expensive and inefficient (ie slow), so if anyone has any ways of either
i) searching without CONCAT columns or
ii) making this solution faster/ more efficient
There is a workaround solution. But I feel this is too crude and performance may not be that good.
where concat_ws( "<*!*>", col1, col2, col3, ... ) like concat( '%', ?, '%' )
Here, I used
'<*!*>' just as an example separator.
You have to use a pattern string as separator which, you are sure that,
- is not part of the place holder value or
- is not part of the generated string when 2 or more columns are concatenated
Refer to Documentation:
It won't skip empty column values but NULLs.