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.

Code:

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?

Cheers

Additional Work:

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.

Solution 1:

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

please share!!

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,

1. is not part of the place holder value or
2. is not part of the generated string when 2 or more columns are concatenated

Refer to Documentation:

MySQL: CONCAT_WS(separator,str1,str2,...)
It won't skip empty column values but NULLs.

more