CountryGirl Posted February 1, 2010 Share Posted February 1, 2010 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 Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/ Share on other sites More sharing options...
schilly Posted February 1, 2010 Share Posted February 1, 2010 what type is that field? Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005115 Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 what type is that field? "Type: double(15,5) Null: Yes Default: NULL" That's what it says. Thanks! Qadoshyah Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005118 Share on other sites More sharing options...
wildteen88 Posted February 1, 2010 Share Posted February 1, 2010 Set your field type to double(15,2). Now numbers will only have two decimal places, eg 123.45 Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005121 Share on other sites More sharing options...
schilly Posted February 1, 2010 Share Posted February 1, 2010 well if it's a year you probably want no zeros so (15,0) Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005123 Share on other sites More sharing options...
wildteen88 Posted February 1, 2010 Share Posted February 1, 2010 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. Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005125 Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 Thank you guys! That works . I'm getting all the tables fixed now! Qadoshyah Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005135 Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 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 Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005136 Share on other sites More sharing options...
gizmola Posted February 1, 2010 Share Posted February 1, 2010 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. Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005143 Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 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 Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005164 Share on other sites More sharing options...
gizmola Posted February 1, 2010 Share Posted February 1, 2010 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. Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005166 Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 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. Link to comment https://forums.phpfreaks.com/topic/190571-too-many-0-in-my-tables-how-do-i-fix-this/#findComment-1005171 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.