SQL Auto Increment multiple times in the same table

Question

I have a basic HTML input form for the built-in admin panel on a website that I am developing for my friend. The idea is for it to publish data written with PHP to the database, which is then spit out onto the index page.

Problem:

Each entry obviously has its own db column (id, name, ep_num, etc.). The identifier is automatically added to the database, the name is collected as $_POST['name'], however for ep_num I would like to assign automatic increments for the name.

EG:

He sends "Hello" once, he is given an ep_num from "1"
He enters "Peace" once, he is given an ep_num "1"
He again enters "Hello", he is given an ep_num from "2"

I had a long working day, and I think that mysql_insert_id()maybe what I'm looking for, I'm just trying to get my brain to work and hoping that one of you guys will help me.

Thanks in advance, Rich

Answer 1

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

Demo on sqlfiddle

Answer 2

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

, id , .