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?

Answer 1

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 ;