MySQL CONCAT, HAVING and JOIN

We probably need to have a separate conversation about why you are using concat
I suspect (tell me if I'm wrong) that it's actually complaining that it doesn't know what you are referring to when you say "address1" as it's in both the referrals and address tables?

Lets rework your query. You want to know if $term is in any of those fields

$sql = "SELECT referral.id
FROM referrals
INNER JOIN address ON address.clientid = referrals.clientid
WHERE referrals.firstname IS NOT NULL
AND address.prim = 1
AND (referral.fullname LIKE '%{$term}%'
    OR referral.firstname LIKE '%{$term}%'
    OR referral.lastname LIKE '%{$term}%'
    OR referral.email LIKE '%{$term}%'
    OR referral.hearaboutus LIKE '%{$term}%'
    OR address.address1  LIKE '%{$term}%'
    OR address.city LIKE '%{$term}%'
    OR address.state LIKE '%{$term}%'
    OR address.zip LIKE '%{$term}%'
    OR address.homephone LIKE '%{$term}%'
    OR address.workphone LIKE '%{$term}%'
    OR address.cellphone LIKE '%{$term}%'
    )";

Code (markup):

and then you can do funky stuff in the order by to get the best match to be at the top of the list.

Now, in my code, I've split out all the tests and I've got a similar thing on one of my client sites - but I don't have all of them as "like" - columns like email I insist on being given in full. I'd do the same for phone numbers too. That might change your query to this which would be faster:

$sql = "SELECT referral.id
FROM referrals
INNER JOIN address ON address.clientid = referrals.clientid
WHERE referrals.firstname IS NOT NULL
AND address.prim = 1
AND (referral.fullname LIKE '%{$term}%'
    OR referral.firstname LIKE '%{$term}%'
    OR referral.lastname LIKE '%{$term}%'
    OR referral.email = '{$term}'
    OR referral.hearaboutus LIKE '%{$term}%'
    OR address.address1  LIKE '%{$term}%'
    OR address.city LIKE '%{$term}%'
    OR address.state LIKE '%{$term}%'
    OR address.zip LIKE '%{$term}%'
    OR address.homephone = '{$term}'
    OR address.workphone = '{$term}'
    OR address.cellphone= '{$term}'
    )";

Code (markup):

I just hauled out some code for you as an example. Each time I do a test I'm giving the result a value. If the name is an exact match they get 4 points. If it's an inexact match they get 2 points. If the person's name matches they get another 2 points and if the address is an exact match they get 2 more, the same with email. Now, you can use your own rules to decide how many points and for what but if you are in a situation where there might be more than one record returned then "weighting" is a really good idea.

select Membership.name, 
CASE
         WHEN `Membership`.`name` = "smith"
         THEN 4
         ELSE 0
      END +
      CASE
         WHEN `Membership`.`name` LIKE "%smith%"
         THEN 2
         ELSE 0
      END +
      CASE
         WHEN `Individual`.`lastname` LIKE "%smith%"
         THEN 2
         ELSE 0
      END +
      CASE
         WHEN `Membership`.`postal1` = '4 oaktree lane'
         THEN 2
         ELSE 0
      END +
      CASE
         WHEN `Individual`.`email` = 'smith@myname.com'
         THEN 4
         ELSE 0
      END
   ) AS `relevance`
from Membership
left join Individual on Membership.id = Individual.membershipId
where ...
order by relevance

Code (markup):

 

Autocomplete should just be calling a file eg /memberships/getnames/ and you should be able to work the query however you like.

Make sure you reference the table names in your query.