Search for city, state, and zip in one text field [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I'm Trying to have a search with city, state and zip in one text field.

<form action="" method="post">
   <input type="text" name="search_开发者_如何学编程loc" />
   <input type="text" name="search_sty" />
   <input type="submit" value="search" />
</form>

$query = "SELECT
       name, street, city, state, zip_code, phone_number, style, description, time
      FROM sesh
      WHERE city
      LIKE '%$search_loc%'
      OR state
      LIKE '%$search_loc%'
      OR zip_code
      LIKE '%$search_loc%'
      AND style LIKE '%$search_sty%'
      ";

This works for one type at a time. So, I can search for state by its self. I want to be able to type: "Chicago, IL 60493" or something and have it pull results. Any ideas on how I could do this? Thank you.

---

You can concatenate the columns you're searching against into a "virtual" column in the result set and then compare the search term against that concatenation like this:

 SELECT * FROM sesh WHERE CONCAT(city, ', ', state, ' ', zip_code) = ?

assuming that the match must be exact. If people can type in, for instance "Chicago, IL" or "IL 60493" then you can revert to LIKE:

 SELECT * FROM sesh WHERE CONCAT(city, ', ', state, ' ', zip_code) LIKE %?%

(where ? stands for the parameter to your query).

In these examples the user's punctuation must exactly match yours (city followed by command and one space, state followed by one space). But if you want to take it a little further, strip out all white space and punctuation from the item entered by the user and do:

 SELECT * FROM sesh WHERE CONCAT(city, state, zip_code) LIKE %?%

---

Option 1 would be to split the entered search term ("Chicago, IL 60493") on the spaces ("Chicago,", "IL", "60493") and then performing the same search action you have already crafted for each of those segments (probably after stripping off any punctuation).

Alternately, and probably more effectively, you could look at using MySQL's Full-Text Searching. Check out this tutorial Using MySQL Full-text Searching for some pointers on how to use it.

---

This should work:

  $query = "SELECT name, street, city, state, zip_code, phone_number, style, description, time
            FROM sesh
            WHERE '$search_loc' like concat('%', city, '%', state, '%', zip_code, '%')
            AND style LIKE '%$search_sty%'";

Just make sure to escape $search_loc and $search_sty to avoid potential sqli attacks.