Weird trouble

[Tapan]

Hello,

I have created a simple table with 4 fields:

id (auto incr. int)
prefix (varchar)
startrange (int) (these are ranges and this is starting value of the range)
endrange (int) ( and this is ending value of the range)

The data in it is like the following:

1, CL, 001, 499
2, AB, 000, 999
3, 66, 001, 999
4, 92, 201, 400
5, G, 1001, 5000
6, CC, 001, 2000

Now the above data has to be searched by a user:

So for eg. if user enters: 66121 it would show the record 3 coz i must search with and without prefix (i.e combine the prefix with start and end range).

But if user enters only: 121 then i have to show: 1, 2, 3, 6 (coz this is without prefix but comes in starting and ending range)

And user can also do: CL then it will show: 1 (I have taken care of this by stripping out the numbers from the user's search and then just searching the alphabets in prefix and show the matching results. So if user enters CL101 then i remove the 101 and just search prefix field for CL and show the results. This one is working fine.

The above 2 are not working for me which are i.e all data specified by user is numeric including or not including prefix...how to work it out ?

Thanks.

[SJH]

Your best bet is to separate the prefix and the number out into distinct variables in your application language of choice, so instead of sending 66121 to the database you have the ability to send 66 and 121 as separate parameters.

With this in mind, the code you'll need will be something like this.

Assume that $prefix is 66 and $integer is 121 for the purposes of this query. If the user hasn't specified either they will be passed in as an empty string and the integer 0, respectively.

SELECT *
  FROM tableName
 WHERE prefix = '$prefix'
    OR ( startrange <= $integer
   AND   endrange >= $integer );

Does this help?

[Tapan]

Your best bet is to separate the prefix and the number out into distinct variables in your application language of choice, so instead of sending 66121 to the database you have the ability to send 66 and 121 as separate parameters.

With this in mind, the code you'll need will be something like this.

Assume that $prefix is 66 and $integer is 121 for the purposes of this query. If the user hasn't specified either they will be passed in as an empty string and the integer 0, respectively.

SELECT *
  FROM tableName
 WHERE prefix = '$prefix'
    OR $integer BETWEEN startrange AND endrange;

Does this help?

Hi,

Sorry but i don't think that will work coz we don't know whether user has specified prefix or not: so it can be 66121 or 121 or it just can be 661 also coz that is also in range..and some users will know prefix but they don't knwo that its seperate coz its shwon to them as 1 single number. Its upto us to do a search with and without prefix.

I think running 2 queries will do the job...i.e if user has specified 66121 (which inlcudes 66 as prefix and 121 as range):

1st query: search just ranges (without prefix): select * from tablename WHERE 66121 >= startrange AND 66121 <= endrange;

2nd query: search prefix combined with range: i don't know how to combine 2 fields and then do search
select * from tablename where 66121 >= prefix + startrange AND 66121 <= prefix + endrange;

Maybe this is how it will work ? Any suggestions on how to combine and compare ?

Thanks.

[SJH]

Ah, gotcha. Sorry about that. Try this query for size instead...

SELECT *
  FROM tableName
 WHERE ( prefix = '$prefix'
   AND   startrange <= $integer
   AND   endrange >= $integer )
    OR ( LENGTH('$prefix') = 0
   AND   startrange <= $integer
   AND   endrange >= $integer )
    OR ( prefix = '$prefix'
   AND   $integer = 0 );

[Tapan]

Ah, gotcha. Sorry about that. Try this query for size instead...

SELECT *
  FROM tableName
 WHERE ( prefix = '$prefix'
   AND   startrange <= $integer
   AND   endrange >= $integer )
    OR ( LENGTH('$prefix') = 0
   AND   startrange <= $integer
   AND   endrange >= $integer )
    OR ( prefix = '$prefix'
   AND   $integer = 0 );

Hi,

Thanks for trying but i must say: that we don't know whether user has specified prefix or not. So we cannot split the value specified by user. Instead we must combine our data (prefix and ranges) and then compare it with user's value. How to do that ?

Thanks.

[SJH]

Oh right, now I'm with you. Sorry, it's been a long day today.

I'm going to have a think about this and get back to you later if I come up with any ideas. Unless someone else can offer any help of course.

[Tapan]

Hi,

I think i have done it using CONCAT. Now what i have done is:

SELECT * FROM tablename WHERE
66121 >= startrange AND 66121 <= endrange OR
66121 >= CONCAT(prefix, startrange) AND 66121 <= CONCAT(prefix, endrange);

This seems to be working great!

Thanks.

[SJH]

Try that with a non-numeric prefix and let me know if it still works

[Tapan]

Try that with a non-numeric prefix and let me know if it still works

Hi,

As said in my first post: If user enters alphanumeric value eg. CL101 then i remove the 101 and just search prefix field for CL and show the results. This one is working fine and my query is like:

SELECT * FROM tablename WHERE prefix LIKE '&#37;CL%';


Thanks.