How to make a primary key to auto increment in a database in php?

Question

I have a database and i am putting data inside. I have one node called key , which is the primary key and other nodes. Now when i put data on my table , i put data in all the nodes except this key node. How do i make it automatically to increase from 0 when i have a new entry? If i run a script to put something in the database , i can see that the nodes have correctly all the data and the key takes the value 0. When i run my script again i get the error :

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY''

From what i understand , because i dont pass anything to this node , the database "thinks" i am passing again a 0 argument so i have the error. How can i fix it to auto increment every time i have a new entry?

This may help future readers: http://stackoverflow.com/questions/19434801/how-to-add-auto-increment-to-column-in-mysql-database-using-phpmyadmin/33686718#33686718 — cssyphus, Nov 13 '15 at 05:53

Mike Brant · Accepted Answer · 2013-01-15T16:11:56.243

4

You need to set the field as autoincrement. You would need to run an ALTER TABLE statement like this:

ALTER TABLE table_name
MODIFY `key` MEDIUMINT NOT NULL AUTO_INCREMENT

edited Jan 15 '13 at 16:11

answered Jan 15 '13 at 01:49

Mike Brant

70,514
10
99
103

i run this through the phpmyadmin query and i get the error : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key MEDIUMINT NOT NULL AUTO_INCREMENT' at line 2 – donparalias Jan 15 '13 at 01:53
@donparalias - Is `key` the right name for your column? If not, change it to your column name. If it is, you need to encase the word `key` in backticks, since it's a reserved word in mysql, which is next to the 1 key. – nickb Jan 15 '13 at 01:57
you are absolutely right i changed the name to keyId and now works like a charm! Thank you Mike! – donparalias Jan 15 '13 at 02:01

Eric J. · Answer 2 · 2013-01-15T01:53:30.893

With MySQL, you would declare the table with the AUTO_INCREMENT keyword when defining your table to achieve this behavior

Example:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

When you INSERT data, you do not specify a value for the primary key. MySQL will automatically use the next available integer value for the key.

UPDATE

You can change this directly within PHP My Admin: go to the table in question and then

Operations->Table Options->Auto-Increment

I already have the table so how can i run a query from phpmyadmin and make this? — donparalias, Jan 15 '13 at 01:54

score 3 · Answer 3 · answered Jan 15 '13 at 01:48

3

id MEDIUMINT NOT NULL AUTO_INCREMENT

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

answered Jan 15 '13 at 01:48

JCurativo

713
6
17

score 3 · Answer 4 · answered Jan 15 '13 at 01:52

3

Start it at 1, not 0. So the first record is id number 1.

What database are you using? in postgres you register a number sequence. In my sql you just use AUTO_INCREMENT when specifying the column attributes

answered Jan 15 '13 at 01:52

Daryl B

525
5
16

How to make a primary key to auto increment in a database in php?

4 Answers4