sql query multiple fields from a single form input

I'm using php/mysql and jquery ui to auto-populate. What I need help with is querying two fields from the db (city, state) from a single input (chicago, il). What is throwing me is how to combine the fields in the query.

Example that works

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM 开发者_如何学运维Zips
WHERE City = $foo // 'chicago'
OR Zip = $foo // '01234'
GROUP BY City, State
ORDER BY State;

What I'd like to do, but not working

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE City = $foo // 'chicago'
OR
City+', '+State = $foo // 'chicago, il'
OR Zip = $foo // '01234'
GROUP BY City, State
ORDER BY State;

(BTW - The state field is an abbreviation.)

The reasoning is that some city names are only 3 or 4 letters and are contained within other city names so the user may need to type a comma and the state to see the auto-suggestion pop-up.

I tried CONCAT_WS on my db and it worked out fine, the following query should work for you.

Syntax for CONCAT_WS is here: first argument is the separator used to concatenate the strings, which are the other arguments

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE City = $foo // 'chicago'
OR
CONCAT_WS(', ',City, State) = $foo // 'chicago, il'
OR Zip = $foo // '01234'
GROUP BY City, State
ORDER BY State;

Split your input string before running your query so that you can do:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE City = 'chicago'
OR Zip = '01234'
GROUP BY City, State
ORDER BY State;

The state becomes irrelevant if they've also specified chicago.

you can try

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM Zips
WHERE City = 'chicago'
OR( City LIKE "chicago,%" AND State LIKE '%illinois')

OR Zip = '01234'
GROUP BY City, State
ORDER BY State;

I would suggest wrapping your string concatenation in parenthesis. You could also use a function like CONCAT() or CONCAT_WS() in MySQL.

And what do you mean by "it doesn't work" ? The clause breaks or ... ?

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
FROM
   (SELECT City, State, Zip, (City +', ' + State) AS CityState
    FROM Zips) as SQ
WHERE City = $foo // 'chicago'
OR
CityState = $foo // 'chicago, il'
OR Zip = $foo // '01234'
GROUP BY City, State
ORDER BY State;