query help!

[Anon]

Hi All;

I have been working on trying to create a query to search on a range of 'numbers' from the database. Actually, if the search was just numbers, I'd be OK. Here's where I run into the problem...

I have a table set up with approximately 5000 entries. The first three fields in the table are as follows:

id range_start range_end
1 0A00 9A99
2 0B00 9B99
3 AA00 ZZ99
4 123A501 123A699

Now... what I want to do is to be able to enter a string to search for, such as 1A46. This number fits the range of id#1 (0A00-9A99) but I have not been able to figure out a way to search the DB for this.

I even tried using the hexdec function to convert the range start and end to decimal. Problem with that is that the first range (0A00-9A99) returns 2560-39577 and the seach string returns 6726 which fits the range. However, id#2 (0B00-9B99) returns 2816-39833, which the search string (1A46 or 6726 Dec) also fits... but it shouldn't due to the range being a 'B' range and I am searching on 'A'.

Now I could search for strings containing an 'A' first, but that wouldn't work for id#3 as the range is not static, like id#1 or id#2... for example, I want to find which range fits the string 'AV55'. The range must first be calculated... AA00, AA01, AA02... AA99, AB00... AV55... FG00... ZZ98, ZZ99. ('...' are gaps in the range, for example only).

My other predicament is that the ranges are not a consitant length. For example, id#1 and #2 are 4 characters, id#4 is 7 characters. Some ranges also vary within the range... range A01 thru A100 (3 characters thru 4 characters).

I'll end this now before I babble on way too much! Any help would be greatly appreciated!

Kyle Stubbins
[e-mail]

[Anon]

Hmm. Maybe I should wake up first before answering. Is the problem that you are trying to search numbers and it is having problems with the letters in the numbers?

If so try a blob as the field properties.

Now I cant promise I know what Im talking about here cause I am still waking up and I dont even know the question. Im just guessing.

[Anon]

Hi John;

You are correct in saying that I am confused by the addition of letters within the number range. The other point I'm not sure if you understood is that it is a 'range' of numbers / letters that I am trying to search... not simply a pattern match. For example, the first example I used, id#1 of 0A00-9A99 would produce 1000 different variations in the 'range'. IE. 0A00, 0A01, 0A02, 0A03... 1A00, 1A01... 2A00... 3A00... 4A00... 5A00... 6A00... 7A00... 8A00... 9A97, 9A98, 9A99 (the '...' being gaps in the numbers for demonstration).

I have tried reading the information on the blob field property, but have found info to be limited and vague. As far as I can tell blob is simply a case sensitive version of the text property... maybe you could elaborate a bit on it.

Another function I ran into today was the range() function. I have being trying to work that one in... I have thought about finding the range_start and the range_end lengths and eliminating those that don't match the $search_string length. Then breaking each code_start and code_end down into single characters (as well as the $search_string), and comparing the first character of the $search_string (say $search_string[0]) and further limit the results by eliminating those that don't match. (IE. $range_start[0] is a number and $search_string[0] is a letter.) Once I have the correct length range_start and range_end, and the letters in the right spot (by comparison), I can check each character of the $search_string against the range between $range_start[0] and $range_end[0]... foreach(range($range_start[0], $range_end[0]) as $number) { code to compare each $number against $search_string[0]; }

It sounds like a lot of work! 🙂 And it's all in theory right now. Hopefully you understand what I am talking about by my description!

Kyle Stubbins
[e-mail]

against the 'range' of code_start[0] (first char) and code_end[0] (first char)