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:

2010290701
2010290702
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:

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!

There may be a MySQL way of doing it, but otherwise:

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

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.

There may be a MySQL way of doing it, but otherwise:

$lastCIDResult = mysql_query('SELECT MAX(cID) FROM customers');
list($lastCID) = mysql_fetch_assoc($lastCIDResult);
if (substr($lastCID, 0, -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.

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?

The short version of the insert code:

$query = mysql_query("INSERT INTO customers (id) VALUES('$cID')");

Yah, but where? When are you inserting?

Well, this is the script:

$cNAME =$_POST['name'];

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

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

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.

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?

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.