Auto increment in phpmyadmin

I have an existing database using PHP, MySQL and phpMyAdmin.

When users become a member on my website, I need the system to create a unique membership number for them using a five digit number. for e.g 83773. I guess it is like generating a random password except I only want numbers for my members. This ID number has to be unique to each member.

Would it be possible for me to set the primary key to auto_increment in my user table and set it to start at 10000 and then auto increment every time a member registers?

Also, is there a maximum number that the primary key ID number would go up to?

Is this a reliable and safe way to use the primary key ID number as a membership number?

There are possible steps to enable auto increment for a column. I guess the phpMyAdmin version is 3.5.5 but not sure.

Click on Table > Structure tab > Under Action Click Primary (set as primary), click on Change on the pop-up window, scroll left and check A_I. Also make sure you have selected None for Default

In phpMyAdmin, navigate to the table in question and click the "Operations" tab. On the left under Table Options you will be allowed to set the current AUTO_INCREMENT value.

Just run a simple MySQL query and set the auto increment number to whatever you want.

ALTER TABLE `table_name` AUTO_INCREMENT=10000

In terms of a maximum, as far as I am aware there is not one, nor is there any way to limit such number.

It is perfectly safe, and common practice to set an id number as a primiary key, auto incrementing int. There are alternatives such as using PHP to generate membership numbers for you in a specific format and then checking the number does not exist prior to inserting, however for me personally I'd go with the primary id auto_inc value.

1. In "Structure" tab of your table
2. Click on the pencil of the variable you want auto_increment
3. under "Extra" tab choose "auto_increment"
4. then go to "Operations" tab of your table
5. Under "Table options" -> auto_increment type -> 10000

In phpMyAdmin, if you set up a field in your table to auto increment, and then insert a row and set that field's value to 10000, it will continue from there.

@AmitKB, Your procedure is correct. Although this error

Query error: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

can be solved by first marking the field as key(using the key icon with label primary),unless you have other key then it may not work.

The easiest way to do this on the latest phpmyadmin is by using the following command;

ALTER TABLE `table_name` ADD `col_name` INT(1) NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`col_name`);

You cannot set a maximum value (other than choosing a datatype which cannot hold large numbers, but there are none that have the limit you're asking for). You can check that with LAST_INSERT_ID() after inserting to get the id of the newly created member, and if it is too big handle it in your application code (e.g., delete and reject the member).

Why do you want an upper limit?

This is due to the wp_terms, wp_termmeta and wp_term_taxonomy tables, which had all their ID's not set to AUTO_INCREMENT

To do this go to phpmyadmin, click on the concern database, wp_terms table, click on structure Tab, at right side you will see a tab named A_I(AUTO_INCREMENT), check it and save (You are only doing this for the first option, in the case wp_term you are only doing it for term_id).

Do the same for wp_termmeta and wp_term_taxonomy that will fix the issue.

(a)Simply click on your database, select your table. Click on 'Operations'. Under the 'table options' section change the AUTO_INCREMENT value to your desired value, in this case: 10000 the click 'Go'. (See the image attached)

(b)Alternatively, you can run a SQL command under the SQL tab after selecting your table. Simply type 'ALTER TABLE table_name AUTO_INCREMENT = 10000;' then click 'Go'. That's it!! SETTING AUTO INCREMENT VALUE image(a)

SETTING AUTO INCREMENT VALUE image(B)