...

View Full Version : Unique Customer Number



badHabitZ
07-28-2010, 06:25 PM
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!

Keleth
07-28-2010, 06:33 PM
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;

Fumigator
07-28-2010, 07:01 PM
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.

badHabitZ
07-28-2010, 07:13 PM
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.

Keleth
07-28-2010, 07:15 PM
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?

badHabitZ
07-28-2010, 07:25 PM
The short version of the insert code:


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

Keleth
07-28-2010, 07:33 PM
Yah, but where? When are you inserting?

badHabitZ
07-28-2010, 08:01 PM
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)");

Keleth
07-28-2010, 08:42 PM
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.

Fumigator
07-28-2010, 08:46 PM
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?

badHabitZ
07-28-2010, 08:56 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum