prefix word to autoincrement

[pradeepjain]

Hi,
I have a mysql table where in i have a field called patient_id which i do an auto increment and its the primary key !! since this number goes on patient card also simply 1,2 will not be gud . Is there a way to prefix some text to it like patient1, patient2 something like that!!!

[Dormilich]

if it were possible to read the auto increment value in the insert process, you could use a function. you should ask the people in the MySQL forum if that’s possible.

[Atli]

Sure:
[code=mysql]SELECT CONCAT('patient ', `patient_id`) FROM `patient_table`[/code]

[pradeepjain]

Sure:
[code=mysql]SELECT CONCAT('patient ', `patient_id`) FROM `patient_table`[/code]

You got ma question wrong ! while auto_incrementi ng can we prefix some text to the autoincremented value!!!?

[Atli]

You got ma question wrong ! while auto_incrementi ng can we prefix some text to the autoincremented value!!!?

Not directly, no. AUTO_INCREMENT only works on integers.

You could create a function that would get the next value for you and use that instead of an AUTO_INCREMENT field.

I wouldn't recommend it tho. You shouldn't format the data like that before inserting it. That sort of thing should be done on the way out, preferably by the front-end code (or in a simple query, like I posted before).

[pradeepjain]

so which is the best way to generate ID numbers!!
1. remove auto_increment .
Store a basic value as patient001 and then get the value from DB and then increment and send it to DB again for next record

or

2 . let it be auto_increment only !! while displaying to user prefix some text and show it to user!

[Atli]

#2

There is no good reason for storing "patient001 " in the database rater than "1".
Keep the database as simple as possible and do stuff like that (adding "patient") in the applications that require it.

I mean, what if, in the future, a second application was to use the same database, but needed the output to read "client001" rather then "patient001 "?
By storing the number only, that is a piece of cake. By storing the "patient" prefix, it becomes a big mess.