Hi all;
First let me say what a great place this is and all the people that offer their solutions. Most times I can usually find what I need by searching and reading, but not this time! 🙂
Anyway, here it goes....
I have a database of info, with an index table and various data tables. The index is consisted of 4 fields and the data tables just two. The structure is as follows:
Index table
field1: series_start
field2: series_end
field3: series_mfgr
field4: series_table
data_table1
field1: data_number
filed2: data_detail
data_table2
data_table3
and so on.
I need to search on the index for a user supplied number, then retrieve the data_detail from the data_table (based on the table name in the index). No big deal, right?
OK, here's the problem. My index contains variable characters and are based on a 'range'. Some are purely numbers, while others are purely letters, while others are a mix of numbers and letters. For example;
Ranges:
101 - 330 (numbers)
AA00 - ZZ99 (numbers and letters - that's zero zero on both)
Those are the most common types of ranges that I will be dealing with... I will concern myself with ranges of letters when the need arises, but I can only assume the priciples of the solution to the numbers will also be the case with the letters.
The ranges are stored like this in the index table;
series_start: 101 (as per above)
series_end: 330
That would be pretty simple to run through and find a match. But what about;
series_start: AA00
series_end: ZZ99
The range is actually, AA00, AA01, AA02, AA03, ... , AA99, AB01, ... , AZ99, BA00, ... , ZZ99.
If someone enters CD01, for example, I need to be able to tell that it falls in the range of AA00-ZZ99. Still got an answer for me? OK, what if I now said that the ranges themselves will vary? They won't always have the same number of characters, the same mix of numbers/letters or in the same positions? Some will rotate, some will not. For example;
AA00-ZZ99 (as per above)
0A00-9A99 (zero A zero zero) the A does not rotate, but all numbers do
101A000 - 101E500 (the letters rotate A through E and the last 3 numbers increment 000-500)
K01-K100 (here we have a letter and 2 digits to start and a letter and 3 digits to end)
Notice in the K01-K100 example that the first data number is listed as K01. I would also like to find this if someone enters K1. That's secondary though.
To make it just a little tougher, there will be various manufacturers with a data_number that falls in the same range. I need to pull all instances where CD01 (our example from above) falls within the range, then display a list of manufacturers, along with the other corresponding data from the index table and let the user choose which one he/she wants. For example, the 0A00-9A99 above... if I enter 0A01 if will fall in this range, but also another range from another manufacturer 0A0000-0A9999.
I am not sure if I will need to first calculate the range of the series from the index table and then check against that. All the data_tables will have the series expanded and stored in their respective tables, but it would be time consuming to have to search through every data table, especially when there will be a few hundred data tables eventually. Ultimately I would like to search the index and pull all instances, have the user choose the one he/she wants and then pull the data from the correct data table.
I just don't really know where to start. I have been reading up on regexes, preg_match, preg_replace and various others, but need a little direction.
I hope I have explained this in enough detail. If you need any other information, please ask, or email me at safecracker4hire@hotmail.com.
TIA!
