[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 780
  • Last Modified:

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

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?


5 Solutions
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?
derekstattinAuthor Commented:
Yes that seems like the best solution! thanks
Glad to be of assistance :)
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
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.
derekstattinAuthor Commented:
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    
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now