CODEWITHSUNDEEP
phpmysqlcodeigniter
Nyxynyx
Nyxynyx

Reputation: 63687

Zip Code missing leading 0 when retrieved from mySQL tables

I am storing 5 digit zip codes in mySQL tables as CHAR(5). However when a zip code with leading zeroes (ie. 02138) is retrieved from the tables, it becomes 2138. Since it is being stored in the tables as 02138 (checked with phpmyadmin), it has to do with PHP stripping off the leading 0? How can I make sure I retain the leading 0?

I'm using quite a long SQL query string using activerecords in codeigniter.

foreach($q as $row){
        /// bunch of code that adds more elements to the $row array

        $data['rows'][] = $row;
    }

Upvotes: 2

Views: 2526

Answers (4)

gaRex
gaRex

Reputation: 4225

As a quick-n-dirty workaround see @tandu version. But here you should find the root of the problem. What happens is clear -- somewhere PHP converts string "012345" into int 12345. So now you should somehow find, who do so and when. var_dump values and see if they string or int.

May be somewhere in your framework you specify column type as numeric, but it actually char in your DB.

Upvotes: 0

Steve
Steve

Reputation: 1402

display it as text instead of as a number

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191789

str_pad($zip, 5, 0, STR_PAD_LEFT);

Upvotes: 4

Aater Suleman
Aater Suleman

Reputation: 2328

What you need is MySQL ZEROFILL. Specify the column as zerofill.

Upvotes: 3

Related Questions