Filling Out the Database

In the previous post, a ticketing system was created using a MariaDB database. Now let’s throw some information into it.

Inserting Employees

Because of the way we created the database our first step will be adding employees. We will start by adding three employees, John Doe, Billy Bob and Matthew Smith.

We will first look at the structure of the table’s columns to remind us what we created.

SHOW COLUMNS FROM employees;

Which should return the following.

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| employeeID   | int(11)     | NO   | PRI | NULL    | auto_increment |
| employeeName | varchar(50) | NO   | PRI | NULL    |                |
| employed     | tinyint(1)  | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

As we can see, we have three fields with the first being the ID (which will automatically increment based off the highest ID), employee’s name and whether or not they are employed. To add the employees we will enter the following.

INSERT INTO employees VALUES(0,'John Doe',1);
INSERT INTO employees VALUES(0,'Billy Bob',1);
INSERT INTO employees VALUES((NULL),'Matthew Smith',1);

To see the list we will use the SELECT command.

SELECT * FROM employees;

Which should return the following.

+------------+---------------+----------+
| employeeID | employeeName  | employed |
+------------+---------------+----------+
|          1 | John Doe      |        1 |
|          2 | Billy Bob     |        1 |
|          3 | Matthew Smith |        1 |
+------------+---------------+----------+

As we can see in our structured table printout, an ID number has been assigned and the employed status is set to “1” or a logical “True”.

Adding Customers

Next we will want to add some customers. Issuing SHOW COLUMNS FROM customers; will return the following.

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| customerID   | int(11)     | NO   | PRI | NULL    | auto_increment |
| customerName | varchar(50) | NO   | PRI | NULL    |                |
| poc          | varchar(50) | NO   |     | NULL    |                |
| email        | varchar(50) | YES  |     | NULL    |                |
| phone        | int(10)     | YES  |     | NULL    |                |
| rep          | int(11)     | NO   | MUL | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

So we know we have to provide four things in order to not error on the insert, customerID, customerName, poc, and rep. We could enter a customer name similarly to inserting employees using INSERT INTO customers VALUES(0,'Some Company','Some PoC',(NULL),(NULL),#);where the # represents an employee number but that will get somewhat long and tedious over time so we will use the insert command a little differently. We will be adding two customers, Dess Farms and L2 Livestock.

INSERT INTO customers (customerName,poc,rep) VALUES ('Dess Farms','E. Thetford',2);
INSERT INTO customers (customerName,poc,rep) VALUES ('L2 Livestock','M. Lee',1);

By entering SELECT * FROM customers; we will see the following.

+------------+--------------+-------------+-------+-------+-----+
| customerID | customerName | poc         | email | phone | rep |
+------------+--------------+-------------+-------+-------+-----+
|          1 | Dess Farms   | E. Thetford | NULL  |  NULL |   2 |
|          2 | L2 Livestock | M. Lee      | NULL  |  NULL |   1 |
+------------+--------------+-------------+-------+-------+-----+

Now we realize that we forgot to provide a method for contacting our PoC (but we can at least start creating tickets since contact info is not required) but we want to add the info to existing customers, we will use the update command to perform this action.

UPDATE customers SET email='ethetford@yahoo.com' WHERE customerID=1;
UPDATE customers SET email='malee@l2.org' WHERE customerID=2;

If we re-execute the select command, we will see the updates reflected.

Creating a ticket

In the last post I suggested some ticket types to populate the ticketTypes table. I actually took the time to enter those examples so once that is done we can go ahead and create a ticket. We will insert a new ticket opened by E. Thetford retarding a tractor radio not functioning.

INSERT INTO ticket (customerID,ticketType,ticketDescription,openDate,rep) VALUES (1,'T','Radio in Tractor 4340 not working',CURDATE(),2);

I’ll add that CURDATE() tells MariaDB to insert the current date.

Next we will add another from M. Lee about an inquiry regarding a system to remotely signal gates that have suddenly opened.

INSERT INTO ticket (customerID,ticketType,ticketDescription,openDate,rep) VALUES (1,'T','Radio in Tractor 4340 not working',CURDATE(),2);
INSERT INTO ticket (customerID,ticketType,ticketDescription,openDate,rep) VALUES (2,'N','Looking for
a solution to notifiy when gates open.',CURDATE(),1);

It should be noted that MariaDB deals with dates in YYYY-MM-DD format.

Adding Detail

Next we will add some detail. In the case of ticket 1 (E. Thetford’s non-function radio), a technician has gone onsite and diagnosed/repaired the issue citing a bad fuse.

INSERT INTO ticketDetail (ticketID,employeeID,detail) VALUES (1,3,'Went onsite and discovered radio was not
 functional. Checked for power and discovered a blown fuse. Replaced fuse and verified radio function.');

Now, when you begin to add larger amounts of text (in our case we set up the detail field as a TEXT field meaning we can have up to 65,535 characters in the field) it can really screw with the printouts so we will filter that out later by changing the logic used in the select command. We can also see when the entry was create thanks to the timestamp in the time field.

Closing a Ticket

Now let’s reflect the ticket is closed by adding a closed date.

UPDATE ticket SET closeDate=CURDATE() WHERE ticketID=1;

Now that we’ve added a close date to the ticket with a ticketID=1, using select we can see that the closeDate field has been populated on ticket 1 so we know it’s been closed. What do we do when we want to view all our our active (open) tickets? We can assume that any ticket which has a closeDate=NULL means the ticket has not been closed…therfore it is still open. We can therefore sort open tickets by entering the following.

SELECT * FROM ticket WHERE closeDate IS NULL;

In our case the only ticket that should be printed is ticket 2, the inquiry for L2 Livestock.

Once data has been provided, we can see exactly what has been updated on the ticket using some logical filters. In this case I have added some additional information to the ticketDetail table.

SELECT time,employeeID,detail,filename FROM ticketDetail WHERE ticketID=2;

Which prints out all of the detail related to ticket 2 minus the ticketID number (we already know it) and the contents of the file (it will look like…the ASCII contents of a pdf file). Everything is timestamped so you know when it was either last created or modified and who it was modified by and any files which have been provided appear as well.

Conclusion

Hopefully this provides some insight on how to utilize and internally built ticketing system. While I would not recommend trying to have employees update this system via the SQL command line (it can be done much more practically though a graphical interface such as HeidiSQL or via the creation of a web app that uses PHP).