Storing data in a MYSQL table with SMALLINT, fix with PHP or JavaScript

Posted on 2007-10-18
Last Modified: 2013-12-13
I have a mysql data base with phone number extensions stored as SMALLINT, length=4, null

When I echo out a phone number onto a webpage, and the extension value is set to 0, because I am using a interger value, the phone extension echo's 0.
(760) 123-4565 0

I do not want this 0 when there is no extension. What is the best way to change this? Should I set the phone number extension as a VARCHAR, so null is not equal to 0. Is it better to use some php or JavaScript to "hide" this 0 when it is echoed?


Question by:derekstattin
    LVL 21

    Accepted Solution

    How about checking that if the extension variable is 0 then don't echo it?

    something like this?

    if($ext!=0) echo $ext;

    Would that be a feasable solution?

    Author Comment

    Yes that seems like the best solution! thanks
    LVL 21

    Assisted Solution

    Glad to be of assistance :)
    LVL 13

    Assisted Solution

    From a desig point of view for future purposes its worth noting that because your using an Integer Value it will always default to zero if you insert a blank string to it ` `

    PHP will respond of course by pulling this value from MySQL. The neatest way of doing this is to modify the field to accept null and then when inserting data use a

    if ($value="")
          $insert_value = "NULL";
         $insert_value = " ' " . $insert_value . " ' ";

    Because youe inserting the value as NULL it will remain blank when you pull it out in PHP. Its also worth noting that an extension number such as 027 will also appear as 27 so it might be worth considering chaning this to a varchar or similar.
    LVL 2

    Assisted Solution

    Actually,  look at your table definitions too.  You can set the default value for newly inserted or updated rows to have a default for each colum; it can be null instead of 0.

    I would still reinforce it with a check before display, but in the old days, system resources where scarse and if you program at the Embedded level you need to slim down all the IFs.

    Author Comment

    Thanks for the help, I have a question about empty fields. If there is no data for a say company which would be set as Varchar, should I always use NULL as a default, what is the problem with leaving it empty? It slows things down?

    name       phone number      company      
    Jon          123 123 1233              Null
    Jane         344 344 3444

    thanks for the help    
    LVL 21

    Assisted Solution

    It won't slow things down, in the database there is a option to set specific fields whether they are allowed to be null or not (null or not null) if you select null, then this field is allowed to be null.

    In this case, you do not need to manually assign a null to say your company field, since in a sense mysql does it for you.

    Hope this answers your question.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    The task A number given should be formatted for easy reading by separating digits into triads. Format must be made inline via JavaScript, i.e., frameworks / functions are not welcome. So let’s take a number like this “12345678.91¿ and format i…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now