[FIXED] CodeIgniter inserting auto increment

Issue

I'm doing a basic add user form for work and I'm trying to add a user to the database however when I try I just get the primary key is being duplicated error. Pretty sure the solution is looking me right in the face but I've hit a wall today lol.

database table struture

dbo.ci_users

id(PK, int, not null)
user_name(nchar255, not null)
user_email(nchar255, not null)
user_password(nchar255, not null)
user_displayname(nchar255, not null)
user_active(smallint, not null)
user_level(smallint, not null)

Adduser_model

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class adduser_database extends CI_Model {

         function __construct()
        {
            // Call the Model constructor
            parent::__construct();
            $this->load->database();
        }

    public function insert_into_db()
    {
        $data = array(
            'id'            => '0',
            'user_active'        => '1',
            'user_level'         => '2',
            'user_displayname'   => $this->input->post('user_displayname'),
            'user_email'         => $this->input->post('user_email'),
            'user_name'      => $this->input->post('user_name'),
            'user_password'      => $this->input->post('user_password') 
        );

        $this->db->insert('ci_users', $data);

    }
}

Solution

You are specifying a primary key value which is the same for all of your inserts. That is why you are getting that error. Remove that line of code and let MySQL specify that value for you as AUTO_INCREMENT means MySQL will assign the next value automatically with every insert:

$data = array(
    'id'          => '0', // <-- REMOVE THIS
    'user_active' => '1',

edit

Looks like you forgot to add AUTO_INCREMENT to your table. This code fix that:

ALTER TABLE ci_users CHANGE id id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;

Answered By - John Conde