SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Weird trouble

  1. #1
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Weird trouble

    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.

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code sql:
    SELECT *
      FROM tableName
     WHERE prefix = '$prefix'
        OR ( startrange <= $integer
       AND   endrange >= $integer );
    Does this help?

  3. #3
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SJH View Post
    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.

    Code sql:
    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.

  4. #4
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, gotcha. Sorry about that. Try this query for size instead...

    Code sql:
    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 );

  5. #5
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SJH View Post
    Ah, gotcha. Sorry about that. Try this query for size instead...

    Code sql:
    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.

  6. #6
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

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

    Code MySQL:
    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.

  8. #8
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try that with a non-numeric prefix and let me know if it still works

  9. #9
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SJH View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •