允许用户在一个字段中搜索两个搜索词

Question

Is there a way I can have a single input field, called "Location" and allow the user to type in either a postcode OR town name to retrieve a business listing? eg. Bristol or BS6 6NX

Below is my table structure currently

Business: (with example data)
id - 1
name - Apples
location - bristol
postcode - bs5 6nx

Locations: (with example data)
id - 1
name - bristol
postcode - bs5 6

My current query is:

$query = "
            SELECT b.id, b.name, b.category, b.thumb_picture, b.location
            FROM business b 
            LEFT JOIN locations l ON b.location = l.name 
            WHERE l.name = ".$dbh->quote($userLocation)."
            OR l.postcode LIKE ".$dbh->quote($userLocation."%")."
            GROUP BY b.name
            ORDER BY b.date DESC";

Which will only work with town names currently.

Update:

How to detect an address or a postal code in a variable

Seems to be a solution, identify if the field is a postcode or town before hand, and then perhaps have 2 queries depending on the result? One to search the postcode column and one to search town?

Answer 1

One solution I've found with a little more digging is below. I first determine if a postcode or a town has been entered. Then do the appropriate query.

function IsPostcode($postcode)
{
    $postcode = strtoupper(str_replace(' ','',$postcode));
    if(preg_match("/(^[A-Z]{1,2}[0-9R][0-9A-Z]?[\s]?[0-9][ABD-HJLNP-UW-Z]{2}$)/i",$postcode) || preg_match("/(^[A-Z]{1,2}[0-9R][0-9A-Z]$)/i",$postcode))
    {    
        return true;
    }
    else
    {
        return false;
    }
}

$ul = "ze3 9";
if (IsPostcode($ul)) {
    $where = "
    LEFT JOIN locations l ON l.name = b.location
    WHERE l.postcode LIKE ".$dbh->quote($ul.'%')."";

}
else {
    $where = "
    LEFT JOIN locations l ON l.name = b.location
    WHERE l.name LIKE ".$dbh->quote($ul.'%')."";
}

$query =    "SELECT b.id, b.name, l.name, l.postcode
            FROM business b
            ".$where."
            ORDER BY b.date DESC";

Can anyone see a fault with this?