增加用户ID

Question

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

Answer 1

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