CODEWITHSUNDEEP
phpmysqlrecordauto-incrementuniqueidentifier
Kevin Oluseun Karimu
Kevin Oluseun Karimu

Reputation: 545

Unique Identifier Mixed Format with Date Prefix (Php / MySQL)

I am creating a ticketing system that will keep track of tickets that a customer creates. The ticket's basic information will be stored in a table 'tickets' who's structure is as follows:

  Primary Key (int 255)
  Ticket_Key (varchar)
  Ticket Number (varchar 500)
  Label
  Date Created
  Delete

and so on..

The issue is that there will eventually be a large amount of tickets and we need a more uniform way of identifying tickets. I would like PHP to create a Ticket Number in the ticket number that will contain mixed values. The date (in format 20111107), followed by a auto incremented value 1001. 1002, 1003, ...). So the Ticket Number will be 201111071001 for an example.

The issue is how do I program this in PHP to insert to the MySQL database? Also, how do I prevent the possibility of duplicate values in the Unique Id in PHP? There will be a very large amount of customers using the table to insert records.

Upvotes: 0

Views: 2470

Answers (3)

liquorvicar
liquorvicar

Reputation: 6106

What about using an auto-increment and combining this with the date field to generate a sequence number for that date and hence a ticketId.

So your insert process would be something like this:

INSERT INTO table (...ticket info...)

You would then retrieve the auto-increment for this row and run a query like this

UPDATE table SET sequence = (SELECT ($id-MAX(auto_increment)) FROM table WHERE date_created=DATE_SUB(CURDATE(),INTERVAL 1 DAY)) WHERE auto_increment=$id

You could then easily create a ticketId of format YYYMMDDXXXX. Assuming you never retro-add tickets in the past this would only ever require these two queries even under heavy usage.

[EDIT] Actually, after looking into this there is a much better way to do this natively in MySQL. If you define two columns (date and sequence) and make them a primary key (both columns) with the sequence field as an auto-increment then MySQL will update the sequence column as an auto-increment per date (i.e. it will start with value 1 for each date).

[EDIT] A table structure along these lines would do the job for you:

CREATE TABLE IF NOT EXISTS `table` (
  `created_date` date NOT NULL,
  `ticket_sequence` int(11) NOT NULL auto_increment,
  `label` varchar(100) NOT NULL,
  [other fields as required]
   PRIMARY KEY  (`created_date`,`ticket_sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

When retrieving the data you could then do something like

SELECT CONCAT( DATE_FORMAT(created_date,'%Y%m%d'),LPAD(ticket_sequence,4,'0')) AS ticket_number, other fields.... FROM table

Upvotes: 1

musefan
musefan

Reputation: 48415

I did something like this before where I wanted to refresh the counter for each new day. Unfortunately I do not speak PHP so you will have to settle for explanation and maybe some pseudo code.

Firstly, create a couple of fields in a config file to keep track of your counter. This should be a date field and a number fields...

LastCount (Number)
LastCountDate (Date)

Then you make sure that your ticket number field in your database table is set to only unique values, so it throws an error if you try to insert a duplicate.

Then in your code, you load your counter values (LastCount and LastCountDate) and you process them like so...

newCount = LastCount;

if LastCountDate == Today 
   increment newCount (newCount++)
else
   reset newCount (newCount = 1)

you can then use newCount to create your ticket number.

Next, when you try to insert a row, if it is successful, then great. If it fails, then you need to increment newCount again, then try the insert again. Repeat this until the insert is successful (put it in a loop)

Once you have successfully inserted the row, you need to update the database with the Count Values you just used to generate the ticket number - so they are ready for use the next time.

Hope that helps in some way.

Upvotes: 0

jogesh_pi
jogesh_pi

Reputation: 9782

as i understand that you want to make one result of two different fields like datefield and ticketnumfield

in mysql you do this through the command:

SELECT concat( datefield, ticketnumfeild ) FROM `tbl_name`

this query return the result like 201111071001

Upvotes: 0

Related Questions