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:

SELECT CONCAT('patient', `patient_id`) FROM `patient_table`

[pradeepjain]

@Atli
You got ma question wrong ! while auto_incrementing can we prefix some text to the autoincremented value!!!?

[Atli]

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