CODEWITHSUNDEEP
phpmysqlsql
uno
uno

Reputation: 867

PHP: How to include first digit zero on INSERT STATEMENT

I'am trying to INSERT number that has first digit of zero

when i try to add prefix 000012345

it will only add 12345

when i try to add prefix 012345

it will only add 12345

my datatype is INT(11)

 $sql = "INSERT INTO circle_call_prefixes (circle, prefix)
         VALUES (?,?)";
         $stmt = $dbh->prepare($sql);
         $stmt ->execute(array($destination , $prefix ));
         $Last_ID = $dbh->lastInsertId();
         $sql_table2 = "INSERT INTO circle_call_destinations 
         (autoNo,destination, source_circle, comment) VALUES (?,?,?,?)";
         $stmt = $dbh->prepare($sql_table2);
         $stmt -> execute(array($Last_ID, $destination, $source_circle, $comment));

thanks

Upvotes: 2

Views: 2289

Answers (3)

Alok Gupta
Alok Gupta

Reputation: 1360

In MySql you can pad fields, if you know the final length, since the length of your column is 11 and if you need to fill it with 0 on left upto the length of your column, then you can use LPAD function like :

SELECT LPAD(12345, 11, '0') FROM tableName;

Upvotes: 4

Zafar Malik
Zafar Malik

Reputation: 6844

As Somail and Hanky suggested that to keep initial as 0, you should change your data type as varchar instead of int.

But if you want to keep your data type as int then you can go with below approach-

Just change your your data type as "MYCOL INT ZEROFILL"

By this if you insert 1 in this column then it will be stored as 0000000001 and if you insert 125 then 0000000125.

But if you are stick to just your own numbers as just 012345 then use varchar.

Upvotes: 1

Somil
Somil

Reputation: 587

Use data type as varchar(11) instead of int(11) if you want 0 as prefix .For integers leading 0's are of no value.

Upvotes: 3

Related Questions