Too many "0" in my tables . . . how do I fix this?

[CountryGirl]

In several of the fields in my tables there are one too many 0's after the number. Like, say, there is a table that has a "YearBuilt" field in it. The year built is there, but after the year built, there are a bunch of extra zeros. Like this "1886.00000." Of course it comes through in the search results and obviously the company I'm working for doesn't like all the extra zeros.

 

In the original microsoft access DB there are not extra zeros in those fields. It's just there in the PHPMyAdmin tables. How do I fix that and get rid of the extra zeros?

 

Thanks!

Qadoshyah

[schilly]

what type is that field?

[CountryGirl]

what type is that field?

 

"Type: double(15,5)    Null: Yes  Default: NULL"

 

That's what it says.

 

Thanks!

Qadoshyah

[wildteen88]

Set your field type to double(15,2). Now numbers will only have two decimal places, eg 123.45

[schilly]

well if it's a year you probably want no zeros so (15,0)

[wildteen88]

well if it's a year you probably want no zeros so (15,0)

Misread the OP. If its a year then why use double,? Why not just use INT if you're storing years.

[CountryGirl]

Thank you guys! That works . I'm getting all the tables fixed now!

 

Qadoshyah

[CountryGirl]

well if it's a year you probably want no zeros so (15,0)

Misread the OP. If its a year then why use double,? Why not just use INT if you're storing years.

 

I'm not sure. I wasn't the one who loaded the information into the PHPMyAdmin. It seems to be working though now with the double 15,0. I may see if INT works better though.

 

Thanks!

Qadoshyah

[gizmola]

Even better, MySQL has a YEAR(4) type.  The only caveat is that the year has to be in the range of 1901 to 2155.

 

Why should you care?  With the myisam engine, a double column requires 8 bytes, whereas a year only requires 1 byte!  Saving 7 bytes per row for both data and index if needed, is always extremely important to consider for a database of any significant size.

[CountryGirl]

Even better, MySQL has a YEAR(4) type.  The only caveat is that the year has to be in the range of 1901 to 2155.

 

Why should you care?  With the myisam engine, a double column requires 8 bytes, whereas a year only requires 1 byte!  Saving 7 bytes per row for both data and index if needed, is always extremely important to consider for a database of any significant size.

 

Thanks for the info! I will keep that in mind. Unfortunately, I have a decent amount of dates that go back before 1901. But, I will keep this in mind if I can use it for any dates.

 

Qadoshyah

[gizmola]

Even better, MySQL has a YEAR(4) type.  The only caveat is that the year has to be in the range of 1901 to 2155.

 

Why should you care?  With the myisam engine, a double column requires 8 bytes, whereas a year only requires 1 byte!  Saving 7 bytes per row for both data and index if needed, is always extremely important to consider for a database of any significant size.

 

Thanks for the info! I will keep that in mind. Unfortunately, I have a decent amount of dates that go back before 1901. But, I will keep this in mind if I can use it for any dates.

 

Qadoshyah

 

If that is the case, then I'd recommend you use an unsigned smallint.  Only requires 2 bytes, and you will not have any issue with range, as it allows values up to 64k.

[CountryGirl]

Even better, MySQL has a YEAR(4) type.  The only caveat is that the year has to be in the range of 1901 to 2155.

 

Why should you care?  With the myisam engine, a double column requires 8 bytes, whereas a year only requires 1 byte!  Saving 7 bytes per row for both data and index if needed, is always extremely important to consider for a database of any significant size.

 

Thanks! I tried using smallint and it doesn't work for the date column I tried it on. The date column I tried it on has to have the date like  "1994-10-11." When I do smallint, it only gives the year. No day & month.

 

Qadoshyah

 

Thanks for the info! I will keep that in mind. Unfortunately, I have a decent amount of dates that go back before 1901. But, I will keep this in mind if I can use it for any dates.

 

Qadoshyah

 

If that is the case, then I'd recommend you use an unsigned smallint.  Only requires 2 bytes, and you will not have any issue with range, as it allows values up to 64k.