php auto generate number from my sql db

Question

I need help in the following query in php. I want to create auto number like 0001, 0002. I am using this query

$query = "SELECT MAX(cast(registration_code as decimal)) id FROM accounts ";  
    if($result = mysql_query($query))
    {
        $row = mysql_fetch_assoc($result);

        $count = $row['id'];
        $count = $count+1;

        $code_no = str_pad($count, 4, "0", STR_PAD_LEFT);
    }

It is working right but the problems is that when i delete any number like 0001, 0002, 0003 and i delete 0002 this create 0004 i want this will create deleted number i mean 0001 to 0003 if missing any that creat

thanks

possible duplicate of [How to find a "gap" in running counter with SQL?](http://stackoverflow.com/questions/1312101/how-to-find-a-gap-in-running-counter-with-sql) — Elon Than, Sep 25 '13 at 07:06
its because you have already stored numbers upto 0003 that determine 003 as its max number. Thus so even you only have 2 numbers left etc.001,003 . It would definetly get 003 as it max thus adding +1 to it will gain you 0004 using MAX() function. — Jhonathan H., Sep 25 '13 at 07:08
Since you use the MAX function you will always get the bigger number created. If you need to recycle deleted numbers, I think you should keep a list of deleted numbers and then pick the smaller one from this list. If this list is empty, you use the above sql statement. — user1498339, Sep 25 '13 at 07:09
@Rohit - if you think the English could be improved, why not edit the question? For many SO users, English is not the first language. This question is pretty clear... — David M, Sep 25 '13 at 07:10
The `mysql_*` functions are **no longer maintained** and shouldn't be used in any new codebase. It is being phased out in favor of newer APIs. Instead you should use [**prepared statements**](https://www.youtube.com/watch?v=nLinqtCfhKY) with either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). — tereško, Sep 25 '13 at 07:10
your are finding largest number if you delete 0002 but still largest number is 0003 then next number will be 0004 — Nanhe Kumar, Sep 25 '13 at 07:10
yes i have already stored till 0003, i have delete 0002 now when i create new it create 0004 but i want i create the missing number like i deleted 0002 then go on — Ada, Sep 25 '13 at 07:11
possible duplicate of [Fragmentation of id's (auto\_increment column) in mysql](http://stackoverflow.com/questions/1824600/fragmentation-of-ids-auto-increment-column-in-mysql) — Mark, Sep 25 '13 at 07:15

score 0 · Answer 1 · answered Sep 25 '13 at 07:12

used this query

SELECT ( accounts1.registration_code + 1) as gap_starts_at, 
       (SELECT MIN( accounts3.registration_code) -1 FROM arrc_vouchers  accounts3 WHERE  accounts3.registration_code >  accounts1.registration_code) as gap_ends_at
FROM arrc_vouchers  accounts1
WHERE NOT EXISTS (SELECT  accounts2.registration_code FROM arrc_vouchers  accounts2 WHERE  accounts2.registration_code =  accounts1.registration_code + 1)
HAVING gap_ends_at IS NOT NULL

score 0 · Answer 2 · answered Sep 25 '13 at 07:14

0

Go fancy. Write a trigger to update next auto increment value on insert. Write trigger on delete to update the next auto increment value to the deleted id if it is lower than the current next auto increment.

answered Sep 25 '13 at 07:14

Andrius Naruševičius

8,348
7
49
78

score 0 · Answer 3 · edited May 23 '17 at 12:28

0

I think you are trying to fill spaces of deleted auto increment primary no.

You can read this references..

Make auto increment fill previously deleted number

How to fill in the “holes” in auto-incremenet fields?

edited May 23 '17 at 12:28

Community

1
1

answered Sep 25 '13 at 07:15

Jhonathan H.

2,734
1
19
28

php auto generate number from my sql db

3 Answers3