Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Jun 2010
    Posts
    68
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Unique Customer Number

    Hey people,

    I am in need of some help, I have tried all sorts of things, but I'm new so I don't know much. I've got a customer database, and I need a unique customer ID each time I insert a new customer into the database, and it needs to look like this:

    Code:
    2010290701
    Code:
    2010290702
    Code:
    2010290703
    The first bit is today's date, and the bold stuff is the unique number I will be getting. So if I insert 10 customers today, the last customer ID will be:

    Code:
    2010290710
    I will NOT be inserting more than 99 customers each day, so this is not a problem. But I need the script to check if today's date already exists, if so, check which number was used last, and then add a new number plus 1. So, if the following ID was used last -> 2010290703 -> Then the next ID would be this number plus one, so it would become -> 2010290704

    If today's date has NOT been used yet, it needs to be today's date with 01 at the end.

    Hopefully someone understands and can point me in the right direction so I can fix this finally

    Thanks in advance for the help!

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    There may be a MySQL way of doing it, but otherwise:

    PHP Code:
    $lastCIDResult mysql_query('SELECT MAX(cID) FROM customers');
    list(
    $lastCID) = mysql_fetch_assoc($lastCIDResult);
    if (
    substr($lastCID0, -2) != date('Ydm')) $cID intval(date('Ydm').'01');
    else 
    $cID $lastCID 1

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    What you're describing is called an intelligent ID, which is an oxymoron, because there's nothing smart about using an ID like what you describe. If you need a date related to an account, then add a date field to your table, but don't embed a date into the table's unique key. Use a synthetic key instead; MySQL makes this really easy with the auto-increment feature.

    I've worked on many-a project where we converted databases away from these types of keys, because they cause more problems than they solve.

  • #4
    New Coder
    Join Date
    Jun 2010
    Posts
    68
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Keleth View Post
    There may be a MySQL way of doing it, but otherwise:

    PHP Code:
    $lastCIDResult mysql_query('SELECT MAX(cID) FROM customers');
    list(
    $lastCID) = mysql_fetch_assoc($lastCIDResult);
    if (
    substr($lastCID0, -2) != date('Ydm')) $cID intval(date('Ydm').'01');
    else 
    $cID $lastCID 1
    Thanks, this works for inserting one customer, when I insert the next, it does not add the +1, so it is the exact same customer number.

    @Fumigator, I am not inserting this as unique key, this is a second ID, which I am using as "customer number" to give to a customer.

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Quote Originally Posted by badHabitZ View Post
    Thanks, this works for inserting one customer, when I insert the next, it does not add the +1, so it is the exact same customer number.
    What do you mean? How are you using it?

  • #6
    New Coder
    Join Date
    Jun 2010
    Posts
    68
    Thanks
    9
    Thanked 0 Times in 0 Posts
    The short version of the insert code:

    PHP Code:
    $query mysql_query("INSERT INTO customers (id) VALUES('$cID')"); 

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Yah, but where? When are you inserting?

  • #8
    New Coder
    Join Date
    Jun 2010
    Posts
    68
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Well, this is the script:

    PHP Code:
    $cNAME =$_POST['name'];

    $lastCIDResult mysql_query('SELECT MAX(cID) FROM customers');
    list(
    $lastCID) = mysql_fetch_assoc($lastCIDResult);
    if (
    substr($lastCID0, -2) != date('Ydm')) $cID intval(date('Ydm').'01');
    else 
    $cID $lastCID 1;  

    $query mysql_query("INSERT INTO customers (cid, cNAME)"); 

  • #9
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Oh right, its because you have date before month... it'll screw up... you need it to go Ymd... otherwise, if you think about it, 20101007 is higher then 20100908. Switch the month/day.

  • #10
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Quote Originally Posted by badHabitZ View Post
    Thanks, this works for inserting one customer, when I insert the next, it does not add the +1, so it is the exact same customer number.

    @Fumigator, I am not inserting this as unique key, this is a second ID, which I am using as "customer number" to give to a customer.
    And why can't you just give your customer the unique ID generated via auto-increment and make that your customer number?

  • #11
    New Coder
    Join Date
    Jun 2010
    Posts
    68
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Keleth View Post
    Oh right, its because you have date before month... it'll screw up... you need it to go Ymd... otherwise, if you think about it, 20101007 is higher then 20100908. Switch the month/day.
    Sorry, it's the same thing hehe I am messing around with it now, trying to fix it.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •