Unique ID Generated by the day of the month

mhoward

I have a form which writes to a MySQL database. The first field in the database (ID) is auto_increment which works great with no problems.

However, the person I have created the form for, wants the ID to be generated by the day of the month so a new ID is started every day. ie 1st Jan 2003 would be 01 then the first record of that day would be 01 so the actual ID would be 0101, the next record would be 0102 etc... until the next day when the first ID would be 0201 and so on.

I have no problem getting the day of the month, but how do I achieve what I am being asked for?

nigeledge

I hope this is what you are looking for.

This function will return a unique id in the following format:
MMDD followed by some random numbers (i.e. 12174533321).

Be sure to make the ID field in your database a BIGINT NOT NULL UNIQUE.

Also, you should definitely run a check to ensure that the id generated doesn't already exist.

Let me know if this is not what you are looking for.

function generate_id()
{
     mt_srand(time());
     return date("md").mt_rand("1000000", "9999999");
}

mhoward

not really what I was after, maybe I didn't explain very well.

Basically, each day the ID needs to start from 01 again. Part of the ID needs to be the day of the month (DD), therefore I need the total ID to be 1701 (17 represents the day of the month and the 01 represents the first record of the day.) As the records are inserted the ID's will increase like 1702, 1703, 1704 and so on. Then, tomorrow, they will be 1801, 1802, 1803 and so on.

Does that make sense?

nigeledge

I see what you mean. That's a tough one. I can think of a possible way of doing it, but it seems too inefficient to me.

Maybe someone else has an idea?

mhoward

I've done it...like you said, the method I have used probably isn't that good a way to approach this, but it is what I was asked for...

here's the code...

dbconnect();

$LastID = mysql_query("SELECT * FROM $urgents WHERE DailyID LIKE '$CurrDay%'");

$Reference = mysql_num_rows($LastID) + 1;

if($Reference < 10)
{
	$Reference = "0$Reference";
}

for today this gives me exactly what I am after - 1701, 1702, 1703 etc...

Thanks anyway