Combining AND, OR, and REGEXP

Databases
1

Hi guys!

Each record (or booking) in the database has certain additional arbitrary values associated with it; each client has their own specific fields, which the delegate is required to enter data for. So, one booking may have two additional client fields, another may have three.

I’ve written an advanced search to allow the team to use these additional fields as search parameters. But even if they’re only searching for data in one of those fields, I need to retrieve all of them for the purposes of displaying the record in the search results.

I have two problems:

  1. AND doesn’t work, even though the data is there, whereas OR does. I need to use REGEXP because of instances where the data isn’t in a consistent format. I need to match both instances, not just the one. How do I coerce MySQL into searching for both parameters, while maintaining the use of REGEXP?
  2. Even if only a single parameter is used for a search, I need to retrieve all records associated with the result. Presently, if I perform a search using only one parameter, only that parameter is retrieved. How do I retrieve all parameters?
SELECT    [...]
    GROUP_CONCAT(DISTINCT bookings_clients_options_data.value SEPARATOR 0x1D) AS clients_options
FROM
    bookings_attendees


    [...]


AND
    (
        bookings_clients_options_data.value REGEXP ('G403')
    AND
        bookings_clients_options_data.value REGEXP ('762650')
    )

Any suggestions would be greatly appreciated.