如何在PHP和mySQL中创建票证参考号

I'm working on a ticketing system in PHP and mySQL. I have a table support with id as my primary key and AI.

I want to add ticket_number, so that when a ticket is submitted each ticket is assigned a unique number. I can't use mySQL to AI a second field, right?

If I give the first ticket a number, then write a query to lookup the last ticket_number in the DB, then I was thinking of doing something like:

$ticket = 1;
$next = $ticket+1;
echo "ticket number: #".$next;

would this work or is there a better way?

I decided to adapt @Himanshu kumar's answer as follows (as it resolves my orgional question) to use the user's id and the timestamp (both of which I'm using already) as the ticket number:

$user_id = 7; //example from session variable
$cur_date = date('dmyHis'); //timestamp ticket submitted
$ticket = '#'.$user_id.'-'. $cur_date;

This will create create a unique variable that has the user's id and the date and time. I've used seconds to make it more unique. I will then use a query to find all messages/tickets with this ticket.

You can achieve by below code. It will generate unique ticket number every time

$brand = '#ref';
$cur_date = date('d').date('m').date('y');
$invoice = $brand.$cur_date;
$customer_id = rand(00000 , 99999);
$uRefNo = $invoice.'-'.$customer_id;
echo $uRefNo;

For a good and unique ID you have much better solutions :

  • You can use a md5 hash based on the microtime (same as uniqid from PHP but more secure)
  • You can use an extra column with unique = true functionnality and a request to get the max of this column and increment in your code before a new insert
  • You can use your support ID as unique entry, it's perfectly doable by inserting first the other field and then get back the ID inserted to update your ticket ID (if its a other component of your table) or show it alone to your users if you consider it as you ticket id..

As advised by MonkeyZeus, you need to take a step back and rethink your schema.

You want to have multiple rows (replies) that are linked together by a single ID (the ticket number).

You definitely want this ID to be the ID of a row in another table.

So you should have two tables:

  • one for tickets
  • another for replies

The first one will be able to hold data that is ticket-specific, including the state of the ticket (open, closed...), who it is assigned to (the id of the user), who created the ticket (again, an id), and possibly when it was opened and closed (though this could be inferred through other means).

The second one will hold data specific to each entry in your ticket (the initial message, and subsequent replies going back and forth).

You may also have other tables (or it could be the same as replies) for other types of actions (ticket status changed, sub-ticket created, etc.).

tickets will have a unique ID which you can use as is as the ticket number (probably with some sort of prefix, possibly reformatted somehow).

replies will have a unique ID (which will be useful when you want to attach files to the reply, or edit it) as well as the ID of the ticket it is associated with.

Let Mysql create IDs. Use you favorite language's API to get the ID of the last inserted row.

  • DO NOT increment IDs client-side based on the max id returned by the server. Race condition looms.
  • DO NOT use IDs that are generated client-side and are not guaranteed to be unique

@jcaron I've designed a schema - would this work? proposed schema for ticket system database