0

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?

jmiller
  • 578
  • 11
  • 28
  • 3
    Upon inserting a record into your `support` table you can use [insert_id](http://php.net/manual/en/mysqli.insert-id.php) to retrieve the AI `id` which was inserted. No need for this `$ticket+1` stuff. – MonkeyZeus Aug 23 '16 at 12:45
  • @MonkeyZeus I could retrieve the row id from a query (I already thought about that) but I need my replies to belong to the same group i.e ticket number not the row number. – jmiller Aug 23 '16 at 12:53
  • I don't get it. How is a ticket not a unique entry in the `support` table? – MonkeyZeus Aug 23 '16 at 12:55
  • Say a user submits a ticket it will get a unique row id. Then I reply to it, my message gets a new / unique row id. I need **ticket_number** to link the conversation messages together... – jmiller Aug 23 '16 at 12:59
  • 3
    You should not do that in the same `support` table; it will become an unmaintainable mess faster than you can blink. Look into a concept called [normalization](https://en.wikipedia.org/wiki/Database_normalization) and separate your concerns by creating additional tables which are linked to each other through primary and foreign keys. – MonkeyZeus Aug 23 '16 at 13:02
  • How? All my tickets will have a `timestamp` and `user_id` and **ticket_number** will link them all together. It's a simple lookup query which I can order by the `timestamp`? – jmiller Aug 23 '16 at 13:04
  • This is precisely why I submitted a close-vote on your question as being too broad. You need to calmly step away from the keyboard and do some research on database normalization for about 3 weeks and then you can tackle your issue/project with much greater efficiency. As it stands, you are preparing to headbutt a wall of knives several times. – MonkeyZeus Aug 23 '16 at 13:12
  • OP, you need to heed @MonkeyZeus's advice. You **need** to have one table for tickets, and another for the individual replies (with a reference to the ticket). Where would you store the status of a ticket otherwise? – jcaron Aug 23 '16 at 16:25
  • Also, never, ever, get the last ID from the database and increment client-side before assigning a new ID. You'll end up with a race condition (when two users create a ticket at the same time, which will result in the same ID being used twice). Either use IDs you are **sure** are unique (i.e. not a timestamp), or let the database do the work for you using AI (on MySQL) or sequences (on PostgreSQL). – jcaron Aug 23 '16 at 16:27

5 Answers5

1

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.

jmiller
  • 578
  • 11
  • 28
  • Welcome to the world of non-unique IDs. So if a user creates a ticket at 12:01:43 and another one at 14:32:43 on the same day, you end up with the same ID. Yes, it **will** happen. – jcaron Aug 23 '16 at 16:30
  • I was talking about the same user creating two tickets on the same day. And the times may be different, but you only use the seconds (43 in both cases) and the date, so they will have the same ID. In any case, see my other comments on why this is all a very bad idea. – jcaron Aug 23 '16 at 16:38
  • No it won't. Ticket one would become 430112 and ticket two would become 433214. Both would be pre-fixed by the user ID. – jmiller Aug 23 '16 at 16:43
  • OK, let's start again. If the date is 23 Aug 2016, 12:01:43, what does `date('sdmy')` return? I can tell you it's not 430112. – jcaron Aug 23 '16 at 16:48
  • I've tested it. In your example it would be something like: 43230816. – jmiller Aug 23 '16 at 16:51
  • Now, same thing for 23 Aug 2016, 14:32:43, what does `date('sdmy')` return? – jcaron Aug 23 '16 at 16:53
  • ok, I see your point. So i'll add mins and hours to the date? – jmiller Aug 23 '16 at 16:54
  • No, [use a separate table with an auto_increment ID to generate ticket IDs](http://stackoverflow.com/a/39106683/3527940). – jcaron Aug 23 '16 at 16:56
  • Note that even with your updated date string, there's still a slight chance there will be a collision. Imagine having a monitoring system which opens tickets automatically when faults are detected. It could very well open multiple tickets within one second. – jcaron Aug 28 '16 at 09:48
1

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
Community
  • 1
  • 1
jcaron
  • 17,302
  • 6
  • 32
  • 46
  • OK thanks. I'll look at this. At the moment, I have a simple table with id, user_id, user_name, subject, message, timestamp. I was going to add status etc next... – jmiller Aug 23 '16 at 16:53
  • `user_name` should probably not be in that table, but in a separate `users` table. – jcaron Aug 23 '16 at 16:55
  • It is but I wanted to store it here also. – jmiller Aug 23 '16 at 16:57
  • Thanks for the detailed answer. I have implemented this schema (see my schema illustration below) and it is working well. :) I have chosen my answer below as it resolved my original question, but thanks for your additional help! – jmiller Aug 27 '16 at 07:49
0

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;
Gerald Schneider
  • 17,416
  • 9
  • 60
  • 78
Himanshu kumar
  • 59
  • 9
0

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..
Greco Jonathan
  • 2,517
  • 2
  • 29
  • 54
  • Even though it's cryptographic properties are not important here, **DO NOT USE MD5**. **MD5 is broken**. Take the good habit of using SHA-256 or better. – jcaron Aug 23 '16 at 16:23
  • Also see comment on question. Do not increment client-side based on data provided by the server. This will lead to a race condition. – jcaron Aug 23 '16 at 16:32
0

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

jmiller
  • 578
  • 11
  • 28
  • 1
    Why would you have both a `ticket_id` and an `id` in `tickets`? Keep a single unique key (the primary key). Whether you use an auto-increment integer ID (which I would strongly recommend) or your own `varchar` ID is up to you, but you definitely don't need both. If you do keep both, the `ticket_id` in `replies` should reference the primary key of `tickets`, not a secondary field (especially if you haven't even set an `UNIQUE` constraint on that one). You need to do some more reading on database design... – jcaron Aug 28 '16 at 09:45