[Resolved] 22 changed to 0022?

csousley

I have an old database that has an int based id field, the int is directly related to a class id number in our classified system. We are updating our classified system and all class ids are now 4 digits, so class 22 is now class 0022 as far as our system is concerned.

This causes a problem when I go to do matches.

I wrote a little php script to update the database, but it does nothing to mysql because it doesn't see the zeros at the front of the number. Here's an example:

mysql> update test set id = 0022 where id = 22;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Anyone know how I can get my database id's changed to 00##?

Thanks!

devinemke

from the mySQL manual on Numeric Types:

Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional display width specification is used to left-pad the display of values having a width less than the width specified for the column.

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL trusts that the data did fit into the original column width.

csousley

Perfect, thanks. And sorry it was in the manual, I try not to do that...