468,510 Members | 1,736 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,510 developers. It's quick & easy.

How to approach area searching

I am creating a database that has four unique numbers the designate an area. Each number will contain no more that 3 digits. These numbers are tied into each other. For Example, An area may be 74:6:2:3. Where,
74 = county
6 = town
2 = Subdivision
3 = Further division.

I am stummped on my development. My client would want to search multiple areas at a time. each number ties back to the preceeding number. The search could also just include the 1st and second number and use wildcards for the 3rd and 4th numbers.

If this is not clear, please let me know. I will try to clarify this a bit more.
Jan 11 '12 #1
8 1290
12,513 Expert Mod 8TB
An example would help to clarify what you're looking for.
Jan 11 '12 #2
Thanks Rabbit.

Consider the following data. Dashes are used like a format mask of xxx-xxx-xxx-xxx.
Expand|Select|Wrap|Line Numbers
  1. 74-6-2-2 = Sub A
  2. 74-6-2-3 Sub B
  3. 74-6-2 = Area A
  4. 74-6-3-2 = Sub A
  5. 74-6-3 = Area B
  6. 74-6 Region A
  7. 74 = County A
The code represents an area. The first number designates a county. The second number designates a Region. The third number designates an Area. The forth number represents a subdivision. In other words, all the numbers are dependent on the number prior to it. I wouldn't have a number unless the number to the left of it is filled first.

The client would need to search by either of the four grouped items.
Jan 12 '12 #3
12,513 Expert Mod 8TB
And they're all separate fields? Or is it all in one field?
Jan 12 '12 #4
That's what I am trying to determine. How should I set the field or fields ups so they can be searched appropriately. I'm designing a new mysql database to replace the current access solutions. In access it has been set as 4 different fields. I'm just not sure if having them all separate will be the best approach when considering how they should be searchable.
Jan 12 '12 #5
12,513 Expert Mod 8TB
They should be separate fields. That is the best approach for any situation. Combining the fields would make searching more difficult.
Jan 12 '12 #6
Thanks, Rabbit.

I suspected as much.

As far as a search clause, it would most likely include nested where statements?
Expand|Select|Wrap|Line Numbers
  1. Num4 = "x" where Num3 = "y" and Num2 = "z" and Num1 = "q"
Am I thinking around the right lines?
Jan 12 '12 #7
12,513 Expert Mod 8TB
Your WHERE should be at the beginning but yeah, that's the basic idea. Also, in most databases, you have to use single quotes to designate a string. Access let's you get away with either, but not MySQL.
Expand|Select|Wrap|Line Numbers
  1. WHERE Num4 = 'x' and Num3 = 'y' and Num2 = 'z' and Num1 = 'q'
Jan 12 '12 #8
Superb. Thanks again, Rabbit!
Jan 12 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by googlinggoogler | last post: by
9 posts views Thread by Anders Borum | last post: by
33 posts views Thread by Geoff Jones | last post: by
3 posts views Thread by Aaron | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.