Creating a custom autonumber

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Creating a custom autonumber

Post by RobertGonzalez »

I am new to PHP and MySQL, so I apologize in advance if this is a dumb question. I have a DB with a field named RequestID. Is there a way using PHP to make this field automatically enter data in the form: XYYMM##### where X is the actual character X, YY is the Year, MM is the month and ##### is a five digit number that resets itself to 00001 everyday?

Any help would be very much appreciated.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

You understand that you most likely will get duplicate numbers?
'X0310000001' might appear 31 times a month if your unlucky. Just making sure...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

OOPS! I should have stated "month"

Post by RobertGonzalez »

:lol:
I'm sorry, you caught something that I hadn't. How about resetting the number every month?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

You probably need to change the below around abit (adding db layer as example), but I just think it might help. Added two figures representing the days as mentioned in previous post.

If it wont work, its a good start.
Good luck.

Code: Select all

<?php
    // get the last field
    $result = mysql_result(mysql_query("select field from test order by field desc limit 1"),0);
    // demonstrate output
    echo $result;
    // check if the month-part in the stored value is other than current month.
    if (substr($result,3,2) != date('m')) {
        // ...if so, generate a new id
        $value = generate();
    } else { 
        // ... or increase the 5 digit 'value'
        $value = generate(substr($result,-5));
    }
    // demonstrative result output
    echo $value;

    // is use a function for easier reading...
    function generate($var=0) {
        // set the stuff we allready know
        $returning = 'X'.date('y').date('m').date('d');
        // if $var is set, i want to increase that number
        if ($var) {
            // increase it
            $var++;
            // get the lenght (as 00001 + 1 = 2,)
            $len = 5-strlen($var);
            // ...and add the zeros needed (see last comment)
            for ($i=0;$i < $len;$i++) {
                $var = '0'.$var;
            }
            // return an increased value
            return $returning.$var;
        } else {
            // if not increasing, return hardcoded start-value
            return $returning.'00001';
        }
    }
?>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Thank you

Post by RobertGonzalez »

Thanks JAM. I am going to be working with this code this weekend. Thanks for the reply.
Post Reply