An opposite wildcard in MySQL, need to match a few letters in string?

Question

I have a problem.

I have a database containing various beginnings of UK postcodes. For examples "bs" for Bristol, ba for bath, etc...

I'm trying to match when someone types there full postcode in. So it'll give the city depending on the first few letters in the postcode.

For example, someone types 'bs3 5qu'. I want the result to show Bristol, which has the postcode 'bs'

In my head this is opposite to a wildcard. But I can't work out a way of doing it?

Here's what i've got.

$location_query = mysql_query("SELECT * FROM search_locations WHERE location_postcode LIKE 'bs%'",$db_connect);

Any advice?

Answer 1

I'm not shure if I understand your problem, but I think this query might help you.

SELECT * FROM search_locations 
WHERE 'bs3 5qu' LIKE CONCAT(location_postcode,'%')

It works as an 'opposite wildcard', concatenating the wildcard to the location_postcode instead of the user's input.

Another option, if we assume that the postcodes are 2 characters long, is to use the mysql LEFT function to obtain a substring as follows:

SELECT * FROM search_locations
WHERE location_postcode LIKE LEFT('bs3 5qu', 2)

Hope it helps!

Answer 2

I think this might help:

mysql_query("

SELECT
    location_postcode,
    CASE SUBSTRING('".$_POST['user_postal_search']."', 1, 2)
        WHEN 'bs' THEN 'Bristol'
        WHEN 'ba' THEN 'Bath'
        ELSE 'Unknown City'
    END CASE as City
FROM
    search_locations
WHERE
    location_postcode LIKE '".$_POST['user_postal_search']."%'

");

Also, if you don't want your SQL server dealing with substrings then just have PHP do it:

mysql_query("

SELECT
    location_postcode,
    CASE '".substr($_POST['user_postal_search'], 0, 2)."'
        WHEN 'bs' THEN 'Bristol'
        WHEN 'ba' THEN 'Bath'
        ELSE 'Unknown City'
    END CASE as City
FROM
    search_locations
WHERE
    location_postcode LIKE '".$_POST['user_postal_search']."%'

");

Answer 3

Okay everyone. The goal was to have a search box that the user could either search a postcode or a location.

The database was set out like so:

location_id   location_name   location_postcode
1             bristol         bs
2             glasgow         g

Some postcodes began with with only one letter and some started with two, like in the database example above. Now the code below I changes the $location_search variable to either the first letter if it's a postcode with only one letter, or two letters if its a postcode with two letters. Now if its a location such as Bristol. It'll ignore the postcode and assign the entire word to the variable $location_search.

Now I just finished this code. It could be better but It worked. Once it's assigned to the variable you can use it to run mysql queries.

<?php //Assign location ID 
    $location_word = mysql_real_escape_string($_POST["location_input"])

    if(!empty($location_word)){

        //Remove spaces
        $location_word = str_replace(" ", "", $location_word);

        //Work out if it's a postcode or a location.
        $location_3rd_character = substr($location_word, 2, 1);     //Get third character
        if(!ctype_alpha($location_3rd_character)){      //Check if it's a number or not
            $location_type = "postcode";
        }
        else {
            $location_type = "city";
        }

        if($location_type == "postcode"){       //Definding the postcode search, strip down to first or second letter.
            $location_2nd_character = substr($location_word, 1, 1);

            if(!ctype_alpha($location_2nd_character)){   //Trim postcode down to one letter, if only one letter in postcode.
                $location_search = substr($location_word, 0, 1);
            }
            else {      //Trim postcode down to two letters, if only two letter in postcode.
                $location_search = substr($location_word, 0, 2);
            }
        }
        else {
            $location_search = $location_word;  
        }
?>

Answer 4

This question is more on your webserver code than the DB. If you're using PHP to retrieve the user's input, here's an example how you'd do it:

<?php

// Retrieve the user's input.
$postCode = $_POST["postCode"];

// Sanitize, making sure it may only contain alpha-numerical characters for security and make lower-case.
$postCode = strtolower(preg_replace("/[^a-z]/i", "", $postCode));

// Retrieve the first two characters from the user's input.
$postCode = substr($postCode, 0, 2);

// Now query the database like you did before.
$location_query = mysql_query("SELECT * FROM search_locations WHERE location_postcode LIKE '" . $postCode . "%'",$db_connect);