sql query multiple fields from a single form input

Question

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.

il_guru · Accepted Answer · 2011-04-05 15:49:13Z

1

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;

answered Apr 5, 2011 at 15:49

il_guru

8,5483 gold badges46 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EvilAmarant7x Over a year ago

Keep in mind that performing a function on the fields will slow down the query as the function will have to be performed on every row in the table. I think a better answer would be to split $foo by ',' and then trim the whitespace. The first term is now the City and the second term, if any, is the state.

Jeffrey Over a year ago

@Evil I'll keep that in mind and use php split if I need to break them up. list($city, $state) = split(', ', $_GET['term']);

Steve Mayne · Accepted Answer · 2011-04-05 15:29:25Z

0

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.

answered Apr 5, 2011 at 15:29

Steve Mayne

23k4 gold badges53 silver badges49 bronze badges

2 Comments

Jeffrey Over a year ago

the city state and zip are the same variable field being passed in

Jeffrey Over a year ago

they are coming in on keystrokes not all at once. so 'chica' would bring up multiple cities, which is fine, then as the user types more 'chicago' it would auto-suggest the different cities in different states named 'chicago' (yes there are some), then as the user defines even more 'chicago, i' it will only auto-suggest chicago, il

ArtoAle · Accepted Answer · 2011-04-05 15:31:16Z

0

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;

answered Apr 5, 2011 at 15:31

ArtoAle

2,9872 gold badges27 silver badges50 bronze badges

2 Comments

Jeffrey Over a year ago

but i can't separate chicago and illinois. they come in together, combined in the same field (i.e. chicago, illinois)

ArtoAle Over a year ago

Well, i think this is not the best way for going on. if you want not to do your search for state names shorter than 4 char you can actually give up giving autocompletion (actually, if it's 3-4 char, the user should not be helped by autocomplete that it's usefull with long words).

tamarintech · Accepted Answer · 2011-04-05 15:32:44Z

0

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 ... ?

answered Apr 5, 2011 at 15:32

tamarintech

1,99013 silver badges18 bronze badges

Comments

Aducci · Accepted Answer · 2011-04-05 15:49:27Z

0

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;

answered Apr 5, 2011 at 15:49

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

Collectives™ on Stack Overflow

sql query multiple fields from a single form input

5 Answers 5

2 Comments

2 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related