Multiple table Fulltext search against two php variables

Question

I have three tables of information where the business_id is the common thread. I have two input fields, one for general search (users can search for whatever they want) and a zip input. On the php side, I catch the general search string and set it to $search and the zip set to $zip.

How can I use the $search variable to MATCH any of the fulltext indecies then limit those matches using the $zip then return only the id from table c?

My database structure is below:

table coup
    id  << this is the my desired information from the search
    timestamp
    business_id
    coupvalue
    startdate
    enddate
    username
    status
    terms
    image
    description
     primary = id
     fulltext = name
table bloc
    id
    address
    city
    state
    zip
    business_id
    phone
    lat
    lon
     primary = id
table bus
    business_id
    name
    timestamp
    category
    subcat
     primary = business_id
     fulltext = name,category,subcat

Any help would be appreciated!

Answer 1

You can use an or condition with the matches:

select
    c.id
from
    coup c
    inner join bus b on
        c.business_id = b.business_id
    inner join block z on
        c.buisness_id = z.business_id
    where
        (match(c.name) against ('$search')
        or match (b.name, b.category, b.subcat) against ('$search'))
        and z.zip = '$zip'

I haven't benchmarked it, but this could potentially be faster:

select
   c.id
from
    (select id, business_id 
     from coup 
     where match(name) against ('$search')
    ) as c
    left join
       (select business_id 
        from bus 
        where match(name, category, subcat) against ('$search')
       ) as b on
        c.business_id = b.business_id 
    inner join bloc z on
        c.business_id = z.business_id
where
    z.zip = '$zip'