0

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

4
Contributors
6
Replies
13
Views
6 Years
Discussion Span
Last Post by Gfaith31
0

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

0

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.

0

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;

Edited by aksahoo17: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.