Storing phone numbers in database

Discussion in 'IT & Internet' started by UKSBD, Jul 31, 2020 at 10:54 AM.

  1. UKSBD

    UKSBD Not a real duck Staff Member

    10,090 1,987
    Pretty sure I've asked similar before but can't find the thread

    On my submission forms I have a field for phone numbers

    When site runs on PHP5 when someone ads a phone number with a starting zero and no spaces
    i.e. 0123456789 everything works fine, the number is added to the database as entered.

    When I update to PHP7 the leading zero is stripped before the number gets to the database, resulting in it being 123456789 added to the database

    The phone number field is currently VARCHAR

    I've been putting off moving to PHP7 just because of this, but can't put it off any longer

    Also note some people also use +44 when adding their number

    Anyone got the best solution for this bearing in mind the numbers are already in the database.
     
    Posted: Jul 31, 2020 at 10:54 AM By: UKSBD Member since: Dec 30, 2005
    #1
  2. WebDesires

    WebDesires UKBF Regular Full Member

    253 44
    you must be casting a variable as an integer or float at some point in your PHP script, you should have a look and make sure this is not the case.

    Also it doesn't appear it could be this but make sure the input field on the front-end is a text field and not a number field.

    If you would like I would not mind having a look with you to help if you would like to hit me up we can get together on AnyDesk and i can look through your code and help you out.

    Thanks
     
    Posted: Jul 31, 2020 at 11:19 AM By: WebDesires Member since: Feb 23, 2016
    #2
  3. gpietersz

    gpietersz UKBF Ace Full Member

    1,256 291
    Is the +44 stored correctly when running on PHP 7 or do you lose the +?

    It might be a good idea to normalise by either always removing the +44 (in which case make sure you reject foreign numbers) or always adding it. It helps if you later want to check for other enquiries giving the same number or similar.
     
    Posted: Jul 31, 2020 at 11:44 AM By: gpietersz Member since: Sep 10, 2019
    #3
  4. UKSBD

    UKSBD Not a real duck Staff Member

    10,090 1,987
    The + symbol gets stripped if there is no space
    enter +441608111111 and it adds 441608111111 to database

    include a space and it doesn't
    enter +441608 111111 and it adds +441608 111111 to database

    As long as people enter a space it's fine
    At the moment I can just manually add the space before adding to main database
     
    Posted: Jul 31, 2020 at 12:46 PM By: UKSBD Member since: Dec 30, 2005
    #4
  5. gpietersz

    gpietersz UKBF Ace Full Member

    1,256 291
    Posted: Jul 31, 2020 at 1:40 PM By: gpietersz Member since: Sep 10, 2019
    #5
  6. UKSBD

    UKSBD Not a real duck Staff Member

    10,090 1,987
    That's all well beyond me :-(

    It uses PDO and my script has

    if (is_numeric ( $value ))
    {
    $newrs->bindValue ( $newid, $value, PDO::pARAM_INT );
    }
    else
    {
    $newrs->bindValue ( $newid, $value );

    So assume something has changed in PDO::pARAM_INT between PHP5 and 7?

    edit : that's supposed to be : P (but without the space, how do we switch off smilies when posting)
     
    Posted: Jul 31, 2020 at 2:12 PM By: UKSBD Member since: Dec 30, 2005
    #6
  7. TopSpek

    TopSpek UKBF Contributor Free Member

    94 9
    Without spaces, PHP treats your input as an integer. But with spaces, PHP treats your input as a string because numbers don't have spaces.

    You may be able to use the strval function. Run the following at your command line:

    $ php -r 'echo gettype( 1234 );'
    integer
    $ php -r 'echo gettype( strval( 1234 ) );'
    string

    My scripts would need something like this:

    $PhoneNumber = strval( $_POST['FieldName'] );

    But I'm guessing you could do something like:

    $PhoneNumber = strval( $value );

    Don't know for sure in your case because I've never used that PDO. You would have to try it. :)

    There's a discussion here about strval:

    https://stackoverflow.com/questions/1035634/converting-an-integer-to-a-string-in-php
     
    Last edited: Jul 31, 2020 at 2:37 PM
    Posted: Jul 31, 2020 at 2:28 PM By: TopSpek Member since: Jul 15, 2019
    #7
  8. TopSpek

    TopSpek UKBF Contributor Free Member

    94 9
    And of course, don't forget about your database security measures when entering external input into your database. My answer above is simplified for your convenience.
     
    Posted: Jul 31, 2020 at 2:36 PM By: TopSpek Member since: Jul 15, 2019
    #8
  9. gpietersz

    gpietersz UKBF Ace Full Member

    1,256 291
    The forum software has left @UKSBD's emoticon as characters but changed some of his code into emojis. Good example of why it should not be automated.

    If the code is handling the phone number, and only the phone number, then I think you want
    PDO:: PARAM_STR instead of PDO:: PARAM_INT

    Space inserted before "P" to stop it being turned into an emoticon
     
    Posted: Jul 31, 2020 at 3:33 PM By: gpietersz Member since: Sep 10, 2019
    #9
  10. TopSpek

    TopSpek UKBF Contributor Free Member

    94 9
    Actually, I think we're supposed to use the BBCode code tags for code. I mean, when you think about it, a colon immediately followed by a letter, isn't exactly good English grammar is it?

    So:

    Code:
    PDO::PARAM_STR
    No spaces. :)
     
    Posted: Jul 31, 2020 at 3:56 PM By: TopSpek Member since: Jul 15, 2019
    #10
  11. UKSBD

    UKSBD Not a real duck Staff Member

    10,090 1,987

    Thanks, that seems to have solved it on my test site.

    Not 100% sure if it affects anything else yet though so will give it a good check over 1st before doing on main site.

    I'll probably still edit them to include the space, I think phone numbers look much better with a space after the STD

    When did it become the norm for people to write phone numbers as
    01608111111 instead of 01608 111111?

    I blame mobile phones :(
     
    Posted: Jul 31, 2020 at 4:14 PM By: UKSBD Member since: Dec 30, 2005
    #11
  12. TopSpek

    TopSpek UKBF Contributor Free Member

    94 9
    Just looked at your code again, and there shouldn't be any need for all that code.

    You don't need to test whether it is numeric; all you need is a single instruction.

    In the following, $Var is an integer:

    Code:
    $ php -r '$Var = 1234; echo gettype( $Var ) . "\n";'
    integer
    
    In the following, $Var is already a string, but there's nothing wrong with casting it to a string:

    Code:
    $ php -r '$Var = strval( 1234 ); echo gettype( strval( $Var ) ) . "\n";'
    string
    
    Another example, using different syntax, $Var is already a string, but there's nothing wrong with casting it to a string:

    Code:
    $ php -r '$Var = "1234"; echo gettype( strval( $Var ) ) . "\n";'
    string
    
    What I'm trying to say is, you don't have to say:

    "If the value is numeric, then cast it to a string, otherwise ... do something else".

    ...when you can just go ahead and assert that it's a string:

    "The value is a string".

    Writing programs thoughtfully can make a significant difference to an application's overall efficiency.

    Yes, I know you're not interested, but I just wanted to have a play around with the BBCode code tags! :D
     
    Posted: Jul 31, 2020 at 5:30 PM By: TopSpek Member since: Jul 15, 2019
    #12
  13. UKSBD

    UKSBD Not a real duck Staff Member

    10,090 1,987
    I'm interested, just don't know what a lot of it means

    I can see why you say I don't need the extra code if I use PDO:: PARAM_STR but previously I had it as PDO:: PARAM_INT (remove the spaces I'm not playing with the BBCode :) )
     
    Posted: Jul 31, 2020 at 6:39 PM By: UKSBD Member since: Dec 30, 2005
    #13
  14. TopSpek

    TopSpek UKBF Contributor Free Member

    94 9
    If you want the value to be a string, then just make it into a string. You don't have to find out what type the value is (integer or string) before you make it into a string; just go ahead regardless and make it into a string, even if it is already a string. With PDO (which I don't really like) I imagine you would just do something like:

    Code:
    $newrs->bindValue( $newid, $value, PDO:PARAM_STR );
    
    ...and that's it, without any of the other code.
     
    Posted: Jul 31, 2020 at 6:56 PM By: TopSpek Member since: Jul 15, 2019
    #14
  15. TopSpek

    TopSpek UKBF Contributor Free Member

    94 9
    Can't say I blame you for not wanting to mess around with the BBCode. For example, the example below demonstrates how easy it is, but I had to use a different character in the end tag - in this case I used a backslash instead of a foreslash.

    To put your code into a code box, just use the code tags as follows:

    Code:
    [code] Your code goes here [\code]
    ...only use a foreslash '/' instead of a backslash in the end tag - if you get what I mean.

    Here's what happened when I tried to use the 'legal' character (a foreslash):

    Code:
    [code] Your code goes here 
    [/code]

    Crazy! :confused:
     
    Last edited: Jul 31, 2020 at 7:34 PM
    Posted: Jul 31, 2020 at 7:26 PM By: TopSpek Member since: Jul 15, 2019
    #15
  16. TopSpek

    TopSpek UKBF Contributor Free Member

    94 9
    Here's a nice little explanation of how to use BBCode:

    https://en.wikipedia.org/wiki/BBCode

    Now everybody in this discussion can go out into the wider world of UKBF and amaze everyone with our message formatting skills! :cool:
     
    Posted: Jul 31, 2020 at 7:55 PM By: TopSpek Member since: Jul 15, 2019
    #16
  17. gpietersz

    gpietersz UKBF Ace Full Member

    1,256 291
    In general, if a "number" is an identifier (phone number, ID numbers, credit card numbers etc.) rather than a quantity (i.e. something it might make sense to do arithmetic with) you should consistently treat it as a string.
     
    Posted: Aug 1, 2020 at 8:59 AM By: gpietersz Member since: Sep 10, 2019
    #17
  18. TopSpek

    TopSpek UKBF Contributor Free Member

    94 9
    Yes, I've heard this before, and although you are locically correct, we should take it only as a general rule of thumb.

    For example, I use INT ZEROFILL for entity IDs because there are some things which I need to do that will work only with numbers.

    For example, I've worked on a couple of teams which use AUTO_INCREMENT to assign IDs to new rows, and MySQL won't increment a string type; AUTO_INCREMENT is for INT types. (Personally, I don't use AUTO_INCREMENT for assigning new IDs to rows by the way; I have my own method which finds the lowest available number.)
     
    Posted: Aug 1, 2020 at 10:13 AM By: TopSpek Member since: Jul 15, 2019
    #18
  19. UKSBD

    UKSBD Not a real duck Staff Member

    10,090 1,987
    How does it know what is numerical?

    if it is;
    123456789 is that treated as numerical, where as;
    12345 6789 treated as a string ?
     
    Posted: Aug 1, 2020 at 10:16 AM By: UKSBD Member since: Dec 30, 2005
    #19
  20. UKSBD

    UKSBD Not a real duck Staff Member

    10,090 1,987
    That was going to be my next question :)

    I use AUTO_INCREMENT for my id numbers int(10)

    For instance, latest listing added was
    https://www.citationsbooster.co.uk/cslisting/232298/

    The nest one added will automatically be /232299/

    Is changing it to PDO:: PARAM_STR going to have an effect on that?
     
    Posted: Aug 1, 2020 at 10:23 AM By: UKSBD Member since: Dec 30, 2005
    #20