Auto increment an Id value(varchar)

[aksahoo17]

Hi Everyone,

As i wannna to be my Primary Key Em_Id auto increment in MySql but it should be a varchar datatype....as i want my id like em001,em002....something like this....
So can any body help....

Thanks,

Ashish

[Agarsia]

use an PHP script, first count all rows in this table

$count = mysql_num_rows(mysql_query("..."));

then get the last row and get the em_id of $last:

while($last = mysql_fetch_array(mysql_query("SELECT * FROM ... LIMIT ".$count.",1"));
) {
$last_em = $last['em_id'];
}

now create the new id

$new_em = "em".(str_replace("em","",$last_em)+1));

and in the insert Query just set "...Em_Id = '".$new_em."'"...

[smantscheff]

AutoIncrement fields are integer in mysql.
You can mirror the auto-increment field in a varchar field and create a trigger which updates the varchar field on insert/update.

[aksahoo17]

Can U plz send me details coding....for it

[smantscheff]

See here and try for yourself: http://dev.mysql.com/doc/refman/5.1/de/create-trigger.html

[aksahoo17]

Thanks a lot.....
but I have some code that can be useful to others....

create table abc(id varchar(20) ,name varchar(50));

Then do the insert operation through this....

insert into abc (id,name)
select top 1 ('e'+right('000'+cast((substring(id,2,3)+1) as varchar(50)),3))
,'xyz' from abc order by id desc;

[Gfaith31]

Thanks for the codes it really helps...:)