CODEWITHSUNDEEP
phpcodeigniter
Jeff Davidson
Jeff Davidson

Reputation: 1929

Increasing user id

I have a question I have a table of users with two fields id which is already the primary key and auto increment however I have another field that is called user_id which is separate that I'll need to have auto increment but the first row inserted will have a value of 10000 in the db and increment after that.

Here's my function so far for creating a user in the database. How can I with as less code as possible have it insert the first row as 10000 and then add 1 after that.

/**
 * Create new user record
 *
 * @param   array
 * @param   bool
 * @return  array
 */
function create_user($data)
{
    $data['created'] = date('Y-m-d H:i:s');

    $this->db->set('username', $data['username']); 
    $this->db->set('password', $data['password']); 
    $this->db->set('password2', $data['password2']); 
    $this->db->set('email', $data['email']);
    $this->db->set('first_name', $data['first_name']);  
    $this->db->set('last_name', $data['last_name']); 


    $query = $this->db->insert('users');

    if ($query) 
    {
        $user_id = $this->db->insert_id();
        if ($activated) $this->create_profile($user_id);
        return array('user_id' => $user_id);
    }
    return NULL;
}

Upvotes: 1

Views: 285

Answers (3)

chris
chris

Reputation: 36947

You could always just create your own table for storing the extra number you want to auto increment, in the same manor mySQL does it. Only difference is mySQL has a built in function to support it on a per table basis for iNT columns. But the built in option is for one row and one row only.

Do as it does in essence by creating a table ID = tinyINT(4), user_id = INT(11)

Insert a new row, ID = 1, user_id = 10000

before you insert into your other table query for this new table get the user_id +1 to it, and update it.. then use that new number to insert with on the other table.

I realize this has the concept of overhead on it for the extra 2 queries but it serves the purpose needed. Actually if you build a JOIN query the right way you can likely do this in all in single query for the most part

Upvotes: 1

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57660

You can use INSERT INTO ... SELECT statement for this. Dont know how you apply it in your database library. But its plain SQL. See my MySQL console log.

mysql> CREATE TABLE `users`(
    id INT AUTO_INCREMENT,
    `name` VARCHAR(10), 
    `user_id` INT, 
    PRIMARY KEY(`name`), 
    UNIQUE KEY (`id`)
);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO users VALUES (null, 'name1', 10000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users SELECT null, 'name2', MAX(`user_id`)+1 FROM `users`;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO users SELECT null, 'name2', MAX(`user_id`)+1 FROM `users`;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `users`;
+----+-------+---------+
| id | name  | user_id |
+----+-------+---------+
|  1 | name1 |   10000 |
|  2 | name2 |   10001 |
|  3 | name3 |   10002 |
+----+-------+---------+
3 rows in set (0.00 sec)

Upvotes: 2

swatkins
swatkins

Reputation: 13640

Try this:

...
$this->db->set('username', $data['username']); 
$this->db->set('user_id', 'MAX(user_id)+1');
$this->db->set('password', $data['password']);
...

And create the first user where user_id is 10000. That way, 10000 is the MAX.

Upvotes: 1

Related Questions