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....



Recommended Answers

All 6 Replies

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."'"...

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.

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

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;

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.