CODEWITHSUNDEEP
phpmysqlhtml
Graham
Graham

Reputation: 11

Trying to replace Full state name to abbreviation in PHP search

I'm trying to replace Illinois with IL in a city /state /zip search; or, any state for that matter. I have states stored as abbr. so I need that to be in the inputed search. I'm trying:

if(isset($_GET['search_loc'])&&isset($_GET['search_style'])) {
    $search_loc = $_GET['search_loc'];
    $search_style = $_GET['search_style'];
    if(!empty($search_loc)||!empty($search_style)) {

        /* HERE!!! */ $search_loc = str_replace('Illinois', 'IL', $search_loc); // ***Here!!!***

        $query = "SELECT
                    name, street, city, state, zip_code, phone_number, style, description, time
                  FROM music_spots
                  WHERE CONCAT(city, ', ', state, ', ', zip_code) LIKE '%$search_loc%' // and so on...

But, this isn't working. Any ideas on how I could do this? Thank you.

Upvotes: 0

Views: 2457

Answers (3)

Daniel
Daniel

Reputation: 1341

you could do a pretty simple function like this:

function abbreviate_state($state_to_abreviate){
    //This information never changes
    $full_state = array('Alabama', 'Alazka', 'Arizona'); //for the 50 states
    $ab_state   = array('AL','AK', 'AZ');
    //Here we search and replace the state with the abbreviation
    return str_replace($full_state, $ab_state, $state_to_abreaviate);
}

you can put this in a function.php and call it whenever you want, wherever you are.

Upvotes: 0

six8
six8

Reputation: 2990

You should really be using geolocation/address services for this. Searching against a SQL database in that manner won't use an index at all and will get incredibly slow as the data set grows.

If geolocation isn't a viable option, you could at least use a fulltext index and have a different table that has city, state, state_abbr, and zip in the fulltext column. So in this example, you would index "Chicago IL Illinois 60605". This would also find results with less of a match like "Chicago IL Illinois 60610" but this would by no means be a geolocation search.

You would then just search the location table with the user's input and join it to the other table:

SELECT 
m.name, m.street, m.city, m.state, m.zip_code, m.phone_number, m.style, m.description, m.time, 
MATCH (loc.location) AGAINST ('Chicago, IL 60605') as score
FROM location AS loc
INNER JOIN music_spots AS m ON m.location_id = loc.id
WHERE MATCH (loc.location) AGAINST ('Chicago, IL 60605');

Or full name:

SELECT 
m.name, m.street, m.city, m.state, m.zip_code, m.phone_number, m.style, m.description, m.time,
MATCH (loc.location) AGAINST ('Chicago, Illinois 60605') as score
FROM location AS loc
INNER JOIN music_spots AS m ON m.location_id = loc.id
WHERE MATCH (loc.location) AGAINST ('Chicago, Illinois 60605');

Upvotes: 1

Timothy Allyn Drake
Timothy Allyn Drake

Reputation: 926

I would strongly suggest storing and retrieving the state name with the database. I can assure you that hard-coding is not the answer.

Upvotes: 1

Related Questions