Zero'S Showing Instead Of Empty Space

[WDjoe]

Hi every, can someone help me out with this problem that I have concerning zero's showing up in fields that I want to show blank if there is no value being called from the database. I read various topics on this issue but I just don't get it.

 

To keep it simple lets say I have a form that has an optional FAX telephone number. If someone that fills in the form does not have a FAX number he or she will omit this field, but when the form is summited to the database a zero gets placed into the fax_telephone field in the database. I would like it to have nothing placed in the database. The way the fax number shows now has the fax number as "0", I would like it not to show anything at all.

 

This is a very simplified query with everything else removed but the fax telephone field that I am using:

$q = "INSERT INTO users (fax_telephone) VALUES ('$fax_telephone')";/code]

 

On my database I have set up like this:

 

[b]Field:[/b] fax_telephone

[b]Type[/b]:INT

[b]Lenngth[/b]:10

[b]Default:[/b] NULL

[b]Collation:[/b] utf8_general_ci

[b]Attributes:[/b]

[b]Null:[/b] (CHECKED)

 

Can anyone tell me what I am have to do to correct this?

 

Many thanks in advance.

[Jonathon]

Empty is not the same as null.

 

I think that you need to insert `fax_number` = null;

 

Also I feel that I should mention that ideally you should try to avoid null fields in tables and perhaps use a join

[Stuart]

Your submitting an empty value as the fax number which is then being typecast by MySQL into an integer because of your INT column declaration.

 

You can either submit the value as NULL (which is easier/nicer to do using prepared statements over standard SQL) or what I'd do is change the column declaration from INT to VARCHAR.

 

It's quite conceivable that someone may include spaces, pluses, dashes and parentheses in their fax number: e.g. (+441902) 281711

 

A fax number doesn't need to be an integer - you're never going to test the value conditionally e.g. fax_number > 1000 or perform arithmetic on them e.g. fax_number * 3.

 

Indeed looking at Larry's e-commerce example he has a field defined as: phone VARCHAR(15), which is a NULL field.

 

I don't think in this case you'd use JOINS because it's a one-to-one relationship? Personally I don't tend to define these as NULL and just query as: fax_number != '' etc... not sure what best practice is though.

[HartleySan]

As an added note, you could run the fax number through a filter that strips out all spaces, parentheses and hyphens. Just run a simple string replace.