Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    6

    Unanswered: Elastix Caller ID lookup string

    I am tring to get my Elastix server to do a Caller ID lookup.

    I have created a new database in phpMyAdmin named CPcallerID

    4 fields
    CallerID (autoIncrement)
    Name (Text)
    PhoneNumText (text) I have the phone number with the dashes in this field 555-444-1122
    PhoneNum (text) Just the phone number 5554441122 in this field.

    What I need to do is lookup numbers in the phone num field. My PhoneNum field has both 10 digit numbers and 7 digit numbers.

    I have tried to use this SQL string

    Code:
    SELECT * FROM `callerid` WHERE `PhoneNum` = Mid("15554442211",5,7)
    I hard coded the number for testing, But I am getting zero results.

    Any help would be appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    on your current design you'd be better off using the right function to compare the rightmost 7 digits, rather than the mid function. mid will work assuming that all numbers ar 11 digits, right will match the rightmost n digits

    However I suspect you need to rethink your design
    first off I don't think you need a caller id, when your phone number is unique enough already, unless more than one person can share the same number (but even if they do how do you expect your app to handle that)
    you are confusing formatting with the actual storage mechanism, there should be no need EVER to store the punctuation, UNLESS for performance reasons.
    a fully qualified telephone number breaks down into 3 components
    country code
    area code
    actual number
    if your app stays in the US then its relatively trivial to keep it that way, however woe betide you if you try to coerce other countries (especially European countries ) trust me the French and UK telephone numbering system although compliant with the international standards isn't used within the country as the interntional standard. ferinstance major connurbations are 0AAA DDD NNNN (Area District Number, prtefixed with a 0), but smaller towns are 0AAAA NNNNNN)... France tends to use pair of numbers
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2010
    Posts
    6

    Search String

    Yes I agree I will be removing the field with the - in them. I will look into the Right function. So far every search string that I put in returns no matches.

    Thanks for the help

  4. #4
    Join Date
    Oct 2010
    Posts
    6
    I have made the modifications to the database and only have 3 fields I am able to get the look up to work now after changing the phone number to a Int type. The test that are working are in the SQL area of MySQL. After doing several tests on my live server I believe that the elastix server is giving me the the phone number as text. My caller ID says +15554441212 I think that would not work as a INT because of the + How can I write the search for the "Number" being text.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so presuming you are are in the US
    if the Elastix server is sending +15554441212, the actual phone number is 5554441212 if dialling long distance if its the same NPA 555 then the number may be in your db as 4441212

    I dont' know the US phone system approach but I would expect:-
    + to indicate its an international number
    1 to indicate its North America
    555 is the geographical area
    444 is the local exhchange
    1212 is the number on that exchange, effectively the number is 4441212

    on of receipt +15554441212 you could be looking for:-
    4441212 (assuming the system was installed inside the 555 area)
    5554441212

    you may be better off handling this within a stored procedure and working out from the supplied number what the information actually is.

    you need to decide if the +1 is significant. int he UK on incoming international calls sometimes I get the fully qualified number, sometimes I get the other international prefix 00

    a telephone number should be stored as a string/text NOT an integer
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2010
    Posts
    6
    Yes you are correct.

    Can you tell me how to write these two statements so that it will lookup "text" instead of the number like it is now?

    Code:
    SELECT * FROM  `callerid` WHERE  `PhoneNum` =right('5554441212',7)
    Code:
    SELECT * FROM  `callerid` WHERE  `PhoneNum` LIKE '%2085554441212%'

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can use the right function to look at the n characters from the right in a coilumn in the db
    eg:-
    where right(phonenum,7) = "4441212"

    or use a wild card match
    where phonenum like "%5554441212" or phonenum = "4441212"

    that will retrieve any rows where the value stored in the db where the number is 4441212, with no STD/NPA area code OR where the last digits in the phine number are 5554441212
    the reason for the or is
    you want rows which are are an exact match for 4441212 where you dont' have the area code in your db or rows where the areacode and number match
    it does mean you need to strip off the leading chjaracters from your Elastixs server. hence why I suspect you may need to think about using a SP
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Oct 2010
    Posts
    6
    Sorry I didn't explain properly.
    SELECT * FROM `callerid` WHERE `PhoneNum` = right('NumberFromElastix',7)

    So Elastix is giving me (at least this is what I see on my caller id) +15554441212
    Everything I am reading online tells me that the variable I use for "NumberFromElastix" is [number]

    Do I have to put anything special when I am looking up the Number variable and it is a text string

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Well try it and see....
    Code:
    ....where phonenum = right(numberfromelastix,7) or phonenum=right(numberfromelastix,10)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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