SQL - query through a text field, which can take on different values
Developing a website and just trying to get back to the swing of (smart) SQL queries and so on, my mind is completely gone today!
There is a website http://www.ufindus.com/ which has a text box that allows you to enter either a place name or a postcode / postal code. I'm trying to do something similar, but I'm rubbish on SQL - since you are building a SQL statement that can potentially look at 2 columns (i.e. space and zip code) because you cannot query both fields for the same values like
place = 'YORK' AND postcode = 'YORK'
or
place = 'YO21 5EA' AND postcode = 'YO21 5EA'
so you need to introduce some logic to be smart enough to determine if it looks like a place name or zip code - it seems too complicated to me! Any help would be much appreciated.
source to share
What happened to trying to match place and zip code? If I put in "York" and (somewhere) that turned out to be a valid zip code, I should get this result. As far as preventing the same thing from being injected twice, well, you can handle this by checking before making the database call.
Oh. I guess I was a little slowed down. Yes ... what others have suggested is correct, "OR" is what you were looking for. I misinterpreted.
source to share
Okay, first I'll assume you have a table showing the postcodes for the posters.
Call this desk "zip" with zip columns and "mailbox". They are both of type char.
Then ... whatever you do, make sure the user login is not part of dynamic sql. Make sure this is a parameter... Otherwise, you are suggesting SQL injection attacks that could really ruin your day. This is important .
Our user input is at @textboxstring.
With that in mind, you can get the postcode and postal message like this:
select @textboxstring = RTRIM(@textboxstring) + '%';
select postcode, postplace
from postcode
where postcode like @textboxstring or postplace like @textboxstring;
Note that I am modifying @textboxstring to get the wildcard with like
, not using dynamic sql.
If the zip was integer, you would need to convert the input to int before sql execution. So with @textboxint you can do this:
select @textboxstring = RTRIM(@textboxstring) + '%';
select postcode, postplace
from postcode
where postcode = @textboxint or postplace like @textboxstring;
Oh, and you need to deal with the fact that your search can have multiple results. You probably only want the first line.
source to share