Allow user to search two search terms in one field

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?

To list the names of businesses in the users given search term. So if I type in 'Bristol' or 'BS6 4NQ' for example. All businesses with a column matches this will appear. — gromey, Dec 31 '16 at 17:23

score 0 · Answer 1 · answered Dec 31 '16 at 17:18

0

$query = "SELECT b.id, b.name, l.name, l.postcode
FROM business b
LEFT JOIN locations l ON l.name = b.location
WHERE l.postcode LIKE '%" . $search_key . "%'
OR l.name LIKE '%" . $search_key . "%'
ORDER BY b.date DESC";

answered Dec 31 '16 at 17:18

Mirek Kowieski

94
4

code-only answers are better than nothing, but it's much better if you can add a few lines to explain what the code is doing ... – Ben Bolker Dec 31 '16 at 18:55
Ok. I will remember next time :) – Mirek Kowieski Dec 31 '16 at 19:24

score 0 · Accepted Answer · answered Dec 31 '16 at 17:57

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?

Allow user to search two search terms in one field

2 Answers2