Formating reference numbers(auto increment)

[fintan]

Hi how would I go about formating a auto increment number in php and mysql.

I need a number say 10 to look like 00010. Thanks

[jatar_k]

treat as a string
check the length
pad with 0's up to the appropriate length

[fintan]

Thanks for the reply jatar_k.

Say when I get up to a 100 would the 000 turn 00 then.

Would it be some thing like this

$string = '100';

$length = strlen ($string);
if ($length = 3) {
echo '000'.$string;
} elseif($length = 3){
echo '000'.$string;
}

[edited by: fintan at 8:04 pm (utc) on Jan. 12, 2005]

[jatar_k]

if that's what you want, when you check the length prepend a zero in a loop until the length equals your desired max length. You shouldn't have to ever change thsi code unless you go over the max.

also look at
[dev.mysql.com...]

and look at the ZEROFILL attribute, sorry, forgot to mention this last time. Then you don't have to do anything with PHP. ;)

[fintan]

Tried that zerofill with

CREATE TABLE `appldetails` (
`applid` mediumint(8) unsigned zerofill NOT NULL auto_increment,

when I do a insert all I get is 1. I must be doing it wrong can you add a default like 0000 to an auto_increment.

[jatar_k]

and when you select it does it only return 1 as well, or does it zerofill then?

[jatar_k]

I just built the table and tried it

mysql> CREATE TABLE `appldetails` (
-> `applid` mediumint(8) zerofill PRIMARY KEY auto_increment);

mysql> describe appldetails;
+--------+--------------------------------+------+-----+---------+----------------+
¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦
+--------+--------------------------------+------+-----+---------+----------------+
¦ applid ¦ mediumint(8) unsigned zerofill ¦ ¦ PRI ¦ NULL ¦ auto_increment ¦
+--------+--------------------------------+------+-----+---------+----------------+

and then did

mysql> insert into appldetails values ('');
Query OK, 1 row affected (0.02 sec)

mysql> select * from appldetails;
+----------+
¦ applid ¦
+----------+
¦ 00000001 ¦
+----------+
1 row in set (0.00 sec)

so it worked for me

[fintan]

Thanks jatar_k your a life saver. Yea it works, tried it there. I made a mistake in my php. I needed that done straight away thanks again.

fintan.