sql only storing 1 request per user, ignores other ones

Question

somehow my mysql database is only storing requests once per user.

I'm planning to create a ticket system for my website, I've created the tables and created a form and a php class see below.

In the case that i would want to create 2 different tickets behind each other it would only store the first ticket but not the second one.

Screenshot of the submitted form

Sql code:

CREATE TABLE IF NOT EXISTS `Comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` varchar(255) NOT NULL,
  `comment_creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `Conversation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(30) NOT NULL,
  `comment_id` int(11) NOT NULL,
  `conversation_creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ticket_id` (`ticket_id`,`comment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `Tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `email` varchar(50) NOT NULL,
  `ticket_id` varchar(30) NOT NULL,
  `ticket_question` varchar(255) NOT NULL,
  `ticket_status` tinyint(1) NOT NULL DEFAULT '1',
  `ticket_subject` varchar(50) NOT NULL,
  `ticket_creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ticket_id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

Php class:

<?php
    class ticket_create extends database {

        // Ticket class constructor storing the class variables 
        function __construct($username, $email, $ticket_id, $ticket_subject, $ticket_question){
            $this->username = $username;
            $this->email = $email;
            $this->ticket_id = $ticket_id;
            $this->ticket_subject = $ticket_subject;
            $this->ticket_question = $ticket_question;
        }

        // Function to add the ticket to the database
        function create_ticket(){
            $this->connect();
            $this->execute_query("INSERT INTO Tickets (username, email, ticket_id, ticket_subject, ticket_question) VALUES ('" . $this->username . "', '" . $this->email . "', '" . $this->ticket_id . "', '" . $this->ticket_subject . "', '" . $this->ticket_question . "')");
        }

        // Function to handle the class and execute their functions
        function class_handler(){
            $this->create_ticket();
            return 'The ticket: ' . $this->ticket_id . ' was successfull created.';
        }
    }
?>

Call to the php class:

<?php
    if(!empty($_POST)){
        require_once('../handling/ticket_create.php');

        $ticket_question = $_POST['ticket_question'];
        $create_ticket = new ticket_create($user->username, $user->email, md5(uniqid($user->username, true)), $ticket_question);
        $ticket_response = $create_ticket->class_handler();
        echo $ticket_response;
    }
?>

How would i get it working, that every ticket would be stored?

*"somehow my mysql database is only storing requests once per user."* - Probably because in the ticket table you have a `UNIQUE KEY` on `username`. If you want more than one ticket per user, remove this constraint. — GarethD, Jan 29 '16 at 12:34
Also, can a comment really belong to more than one ticket? You would typically only use a junction table if it could (i.e. many to many relation), otherwise if a comment can only appear on one ticket, you could just add a foreign key `ticket _id` to `conversation` (and any other required fields) and remove the need for a junction table. — GarethD, Jan 29 '16 at 12:37
I'm not really that good in sql, and no a comment should only belong to one ticket. ill give it a try, thank you guys! :D — stackxquestion, Jan 29 '16 at 12:40
I would also be inclined to make `id` the primary key of your ticket table instead of the varchar(30) field `ticket_id`. MySQL can only cluster around the primary key, so clustering on a varchar(30) field is not only likely to cause fragmentation, it also makes your other indexes wider (because this field must be stored against all indexes). You can still have a unique `ticket_id`, but this would be better as a UNIQUE KEY with id as the PRIMARY KEY. — GarethD, Jan 29 '16 at 12:46
So i could actually remove the conversation table and add a foreign key ticket _id to conversation ? And i could connect them together? — stackxquestion, Jan 29 '16 at 12:56
Exactly. I personally would do something like this - http://sqlfiddle.com/#!9/60c7c7 — GarethD, Jan 29 '16 at 13:09

score 3 · Accepted Answer · answered Jan 29 '16 at 12:39

The problem is this line:

UNIQUE KEY `username` (`username`)

username is unique, meaning if you save banana once as the username, you can't save banana again. Ever.

Remove the unique key from username in your table and it'll all work.

ALTER TABLE Tickets DROP INDEX username;

or

CREATE TABLE IF NOT EXISTS `Tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `email` varchar(50) NOT NULL,
  `ticket_id` varchar(30) NOT NULL,
  `ticket_question` varchar(255) NOT NULL,
  `ticket_status` tinyint(1) NOT NULL DEFAULT '1',
  `ticket_subject` varchar(50) NOT NULL,
  `ticket_creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ticket_id`),
  UNIQUE KEY `id` (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

sql only storing 1 request per user, ignores other ones

1 Answers1