display unsigned zerofill autoincrement value without leading zero

Question

my primay key is user_id which is auto incremant UNSIGNED ZERO FILL, i want to display user_id on a page without leading Zero( means 1 instead of 0000000001 ).. how can we do that in php or in mysql one more question...

if i delete last record( let user_id=21) wwhich have highest id of that table then can we adjust auto increment value to 21 instead of 22 of that table.

Answer 1

To display in PHP without leading zeros:

echo (int) $val;

To set the auto_increment value (this is not good practice, you should let MySQL handle primary keys for you!):

mysql> CREATE TABLE test( id INTEGER AUTO_INCREMENT, data TEXT, PRIMARY KEY id(id) );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO test VALUES (1,'23'),(2,'32'),(3,'3232');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(id) FROM test;
+---------+
| max(id) |
+---------+
|       3 |
+---------+

1 row in set (0.00 sec)

Then delete from test, check if the auto_increment is one above the max(id) in PHP, and if not:

mysql> ALTER TABLE test AUTO_INCREMENT=3;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

As you had 3 records, auto inc was 4, deleted 1 so 2 records, auto inc should be the next value so set it to 3.

As above, this is not good practice and you could end up with messy data if you don't lock the tables while performing these auto_inc queries - sacrificing performance for primary key order doesn't seem sensible!