CodeIgniter inserting auto increment

Question

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);

    }
}

Yes I tried it but I am now getting this error Cannot insert the value NULL into column 'id', table 'ClientPortal.dbo.ci_users'; column does not allow nulls. INSERT fails. — Sam, Aug 17 '16 at 12:11
if id is auto_increment remove 'id' => '0', from array and try — AmmyTech, Aug 17 '16 at 12:15
@Piglet add this ALTER TABLE `ci_users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; — Rajkumar R, Aug 17 '16 at 12:19
Will affect any of the values that are already int his database? as we have 20 users already in there just wondering if this change will delete them? — Sam, Aug 17 '16 at 12:22
Im getting this now Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'MODIFY'. — Sam, Aug 17 '16 at 12:36
@Piglet try like this ALTER TABLE `ci_users` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT; — Rajkumar R, Aug 17 '16 at 13:15
Is there a way I can get the same idea out of this but not by changing the database properties for instance could I use a select max query then increase it by 1 for ID I understand this is no doubt bad practise but for the meanwhile it could work? — Sam, Aug 17 '16 at 14:16

John Conde · Accepted Answer · 2016-08-17T12:24:04.860

7

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;

edited Aug 17 '16 at 12:24

answered Aug 17 '16 at 12:03

John Conde

217,595
99
455
496

Thanks for the reply, upon removing this I got this error, Cannot insert the value NULL into column 'id', table 'ClientPortal.dbo.ci_users'; column does not allow nulls. INSERT fails. – Sam Aug 17 '16 at 12:11
@Piglet I have updated my answer to show you how to add the auto increment to your table – John Conde Aug 17 '16 at 12:24
What I mean is this is what I get Im getting this now Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'CHANGE'. – Sam Aug 17 '16 at 12:59

score 0 · Answer 2 · answered Aug 17 '16 at 12:32

First of all you have to change in database and make AUTO_INCREMENT id then use the following code. It will helps you 

public function insert_into_db()
{
    $data = array(

        '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);

score 0 · Answer 3 · answered Nov 25 '21 at 02:57

0

If you are using a model definition from your table, be sure to change the boolean parameter:

protected $useAutoIncrement = true;

answered Nov 25 '21 at 02:57

Rafael Fuentealba

1

CodeIgniter inserting auto increment

3 Answers3