Page 1 of 1

Reference Number based on date

Posted: Mon Apr 11, 2011 7:40 pm
by lowededwookie
Hello,

I am wanting to create a PHP based job logging system more as an exercise in database driven web development but I have a problem I would like to solve.

I want to create a reference number that is based on the date in the following format:

RN1104120001

So RN is simply just a label to give a bit of clarity so it doesn't just look like a huge number.

110412 is YMD in two digit format.

0001 is the job number for that day.

So essentially the reference numbers will end up like so:

RN1104120001
RN1104120010
RN1104150001
RN1104150010
etc.



How can I create this number so that the last four digits will reset themselves to one the next day?

How can I get this so that it does checking to ensure that the last four digits are always different and never the same?

I can do this in FileMaker Pro but I want to create a web based system and I don't have the money for FileMaker Server as much as I want it.

Thanks so much for any assistance you can give me.

Re: Reference Number based on date

Posted: Mon Apr 11, 2011 10:40 pm
by klandaika
I would assume you have a table where you keep all records for the jobs,
It should have the refference field and the date field when the job was recorded.
Then you could do something like this

Code: Select all

<?php
	//execute the following sql statement to find how many jobs were recorded today
	$sql = "SELECT count(*) FROM JobTable WHERE JobDate = '".date("Y-m-d")."'";
	//store the count in $jobCount variable
?>
Then you do this:

Code: Select all

<?php
	$jobCount = $jobCount + 1; 
	$refNum = "RN".date("Ymd").str_pad($jobCount, 4, "0", STR_PAD_LEFT);
	//then insert the $refNum into your JobTable
?>

Re: Reference Number based on date

Posted: Mon Apr 11, 2011 10:54 pm
by lowededwookie
Thanks for the reply.

I understand most of this but could you explain a little more about this section:

str_pad($jobCount, 4, "0", STR_PAD_LEFT)

What does it do?

Also will this reset the counter so the next day it will start from 0001?

Once again thanks heaps for your help.

Re: Reference Number based on date

Posted: Mon Apr 11, 2011 11:00 pm
by klandaika
It adds "0" to the left side of the $jobCount so that you will get RN1104120001 instead of RN1104121

Re: Reference Number based on date

Posted: Tue Apr 12, 2011 4:06 am
by lowededwookie
I thought so. Thanks heaps for helping me to understand this.

One last question.

With regards the date will "Ymd" give me two digits or would it be more like "yMD"? I need two digits for each the Year, the Month, and the Day for it to work.

Once again I really appreciate you help. Thank you.

Re: Reference Number based on date

Posted: Tue Apr 12, 2011 8:44 am
by klandaika
You are right Y will give you 4 digit year MD however give you textual month and day (ex. SunMay).
so what you need is ymd :) which will be 2-digit year 2-digit month 2-digit day, it automatically prepends 0 to the month and day if necessary.

Re: Reference Number based on date

Posted: Tue Apr 12, 2011 6:41 pm
by lowededwookie
Thanks for that.