CODEWITHSUNDEEP
phpmysql
Filthy_Rich
Filthy_Rich

Reputation: 665

SQL Auto Increment several times in the same table

I have a basic HTML input form for an inbuilt admin panel on a website that I'm designing for a friend of mine. The idea is for him to post data scripted with PHP to the database, which is then spat back out onto the index page.

The issue:

Each entry obviously has its own column in the db (id, name, ep_num etc). The id is auto incremented into the database, the name is gathered as a $_POST['name'], however for ep_num, I would like to assign auto increments per name.

EG:

He posts "Hello" once, it is given the ep_num of "1"
He inputs "World" once, it is given the ep_num of "1"
He inputs "Hello" again, it is given the ep_num of "2"

I've had a long day at work, and believe that mysql_insert_id() may be what I'm looking for, I'm just struggling to put my brain to work and was hoping one of you guys to aid me.

Thanks in advance, Rich

Upvotes: 4

Views: 567

Answers (2)

splash58
splash58

Reputation: 26153

insert into t (name, ep_num) select 'b', count(*)+1 from t where name='b';

Demo on sqlfiddle

Upvotes: 3

Amit.S
Amit.S

Reputation: 441

INSERT INTO table (name,ep_num) VALUES ("hello",1)
ON DUPLICATE KEY UPDATE ep_num=ep_num+1;

To use this statement you need to make column name a primary key or a Unique index and make id an auto increment value if you are changing name to primary key. For more information on Duplicate key update

Upvotes: 0

Related Questions