Searching table for field that contains string?

Question

I am building a search using PHP which searches for cities in a state. My question is how do I get my search to catch this situation...
If someone searches for Highland in the state of TX (for example)... it should return Highland Park and Highland Village... Also... my states are in a drop down list, but what if someone enters Dallas, TX... how do I get it to recognize the Dallas and ingnore the TX... or better yet... how do I get it to recognize TX as a state and Dallas as the city?

And the like operator is not doing it. It is catching extra letters for example... Ddallass and missing letters like dallas, but not what I am wanting it to do by catching Highland and finding Highland Park and Highland Village.

Answer 1

for the first part, if you are using a myISAM table to store this data you can use full text indexing (recommended for this situation), otherwise you are forced to use the LIKE operator (city LIKE "%$city%").

For the second part, you will need to parse out the user entry and match each section against a state table.

Example:

$string= $_GET['search'];
$state = $_GET['state'];
$entryArray = explode(" ",$string);
$search = "";
foreach($entryArray as $entry){
//search through state table using your db setup
//if it isn't in the state table
$search.= " $entry";
//else if it was in the table
$state = $entry;
}
$search = trim($search);
?>

Answer 2

whats that mean