[Resolved] help w/numerical range determination

chet23

Howdy all y'all.. I've been lurking in the shadows for quite some time now.. I usually find the answers I need just by snooping around. This time I haven't found the answer I'm looking for so decided, finally, join and post.

Its prolly a question with a ridiculously simple answer that's already posted here somewhere, but like I said, I can't find it, so here goes..

I have a list of data elements that are ordered by a range of numbers. For example:

001 thru 004 = item #1
003 thru 013 = item #2
013 thru 123 = item #3
133 thru 666 = item #4 ... et cetera ad infinitum...

As you can see, there is some overlap between some of the ranges, and there are some numbers that don't fall into any range at all (ex: 125).

*Note: None of this data is in a db yet as I am yet to receive it. I plan on using MySQL once the data's handed off to me.

I guess my question is twofold: I don't know how to get php to find the right item# based on an entered number, and I dont' know how to best set up the db to work with the method of retrieval. Entering "002" should return item #1. Entering "003" should return both items #1 and #2. Entering "125" will return a message stating nothing could be found.

Oh yeah.. I almost forgot.. Some item # will have two or three or four ranges. Example: item#23 = 001-006, 123-555, 678-999

I guess i could create a db that has one row for each number 001, 002, 003, 004, etc... But that seems like it would be too big especially if I'm adding several k numbers.

Should each item have its own row perhaps? And then a field for each the high and low numbers of its range? That wouldn't work because of the multiple ranges some items have - unless i listed each multiple item on multiple rows.

I just don't know where to begin with this. Especially since I don't know quite how to work the php or the MySQL for the task. I always try to think through my method so I don't get up to my ankles in a misguided project.

Doth my question senseth make?
Any help on this would be greatly GREATLY appreciated.

A thousand thanks to any and all who give this little problem of mine even a passing thought.

-Chet23 😕

LordShryku

Well, I'm not sure how often this data will change, or how many items/numbers you'll have, but with the above example, I'd make myself a nice little multidimensional array for the data. Maybe put it in a seperate file if it's going to be updated a lot. Use the items as the keys, use [man]range[/man] for the other numbers. Something like this

$numbers = array(1 => range(1,4),
                 2 => range(3,13),
                 3 => range(13,123),
                 4 => range(133,166)
);

Then, when I wanted to find what item something was in, I'd do something like this...

$num   = 3; // number to search for
$found = array(); // blank array to store results
foreach($numbers as $k => $n) {
   if(in_array($num,$n)) {
     $found[] = $k;
   }
}

if(count($found) > 0) {
   foreach($found as $f) {
      echo "Found in item #".$f."<br />";
   }
}
else {
   echo "Not found";
}

Just an idea...

paulnaj

As I understand it, for each item in 'items' table, say, you need to store a list of numbers particular that item, and you're thinking about how to build a 'numbers' table to link to.

Depends on the 'structure' of the numbers ...

Are all the numbers in the form range_min thru range_max, like the examples shown?

If so, the answer is, add a range_min and a range_max field to the items table.

Then, suppose you wanted all items whose range included a particular number, 5, say, would be:

SELECT *
FROM items
WHERE range_min <= 5
AND range_min >= 5

Let me know if this doesn't make sense or if the number 'structure' could be different to above. If so, show us the possibilities.

edwardsbc

If this needs to be really flexible do something like this.

Create an Items table to handle your items...

Tbl_ITEMS FIELDS: ItemID, Item, etc.

Create a ranges table to handle the ranges..

Tbl_RANGES FIELDS: RangeID, ItemID, Min, Max

Now you can add as many items as you want, and as many ranges for each item as you need. To see if an item falls within a range, you do a join on the tables.

If you want to find all the items within the matching ranges of $yournumber, something like this should work:

SELECT Tbl_ITEMS.* WHERE Tbl_ITEMS.ItemID=Tbl_RANGES.ItemID AND (Min<=$yournumber AND Max>=$yournumber)

I haven't tested this syntax, but the theory is sound.

chet23

Thank you all for taking the time to share your thoughts on this, and offering your suggestions. Each solution sounds like it'd fit the bill.

Is there a benefit to using an array over a db in this instance?

What I'm actually attempting to do is this:
I have a list of distributors with addresses, and general contact info. Each distributor services a geographic area based on the first three digits of a zip code. Some distributors service ZIPs 902-909, some service 906-923 etc... I have a list of the geographic zones that the first three digits of ZIP codes refer to. When someone enters a ZIP code, I want to return distributor(s) that service their geographic area, not necessarily the closest distributor. Otherwise I'd be looking into an actual ZIP/distance script.

Does this make sense?

In light of this, I am leaning towards either of the two second suggestions - the ones using a db.. Now my question is, are there pros and/or cons to using a single table such as paulnaj suggested, versus using two data tables and joins like edwardsbc suggests? I have little experience with joins, but if its a practical solution, i'm up to the task of learning how to do it.

Thanks again for your time and consideration. All your input has been very illuminating and helpful.

-Chet23

edwardsbc

Using joins (or not) can depend on several factors. In an "Ideal" database design you would always join and highly normalize your tables. Normalization (in the database world) means to reduce or eliminate the storage of duplicate data. My join scheme enables you to store as much data as needed without ever duplicating any of it.

However, joins do have performance implications. Sometimes it does make sense to have a table which is NOT highly normalized just for speed purposes. But I have found such tables become hard to manage and error prone eventually. If you find you do have performance issues, you can always generate a Non-normalized "lookup" table (that will be a fast, inexpensive database operation) by periodically generating it from joins of well normalized tables. The lookup table is never modified itself except when regenerated from the other tables.

Joins are not that difficult to learn once you understand what the database engine is doing with them. I suggest you add "JOIN" to your skill set.

chet23

edwarsbc - thanks for the help... Thanks to you ALL for your help, and for taking the time to respond.

I'm going to move forward with doing the table JOINS.. I think it'll work out alright. Who knows, I may wind up posting another question related to the joins, but I doubt it - I'm sure I'll be able to find some older posts that address whatever questions I might have. The boards here are great and extremely helpful.

Thanks again.

Adios - Chet23