how to create automatic series number which are primary key for that table which has the following series JTM0605160001..... in SQL

Question

I want to create a automaprimary key(auto-increment) which is start with JTM0605160001. Here

JTM: would be constant
060516: date in ddmmyy format (06-day 05-month 16-year
0001 would be iterate

So when the user want to input the data, there is already have a number series in that form. so it will be easy for me to check their data only based on the form's series number. so can you help me with some tutorial and coding?

I am using XAMPP control panel and Dreamweaver cs6.

I want serial number like this:

JTM0605160001
JTM0605160002
JTM0705160003
...

so the form will be like this:

no series : JTM0605160001 (automatic provided when user want to fill the form)

name : sally (user will fill this form)

age : 34 (user will fill this form)

you gotta explain others what you have trued so far with steps or sample code if applicable — Gandhi, Jun 01 '16 at 04:13
Did my answer resolve the issue? If so, please consider accepting it as an answer, otherwise please post a follow up comment. — Frederic Klein, Jun 03 '16 at 07:49

score 0 · Answer 1 · answered Jun 01 '16 at 08:52

table schema:

CREATE TABLE combinedKey ( 
    id INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
    prefix VARCHAR(3) NOT NULL,
    datePrefix VARCHAR(6) NOT NULL,
    PRIMARY KEY (id, prefix, datePrefix),
    age INT(3),
    name VARCHAR(30)
);

So, we combine three columns to a primary key consisting of your auto-incrementing id (note the ZEROFILL for the leading zeros), your constant prefix and the date prefix (your format is not a default date format, so we format it on INSERT, see below), based on INSERT-date.

An insert could look like this:

INSERT INTO combinedKey (`prefix`,`datePrefix`,`age`,`name`) VALUES ('JTM',DATE_FORMAT(NOW(),'%d%m%y'),34,'Sally' );

A select statement with output of series number

SELECT CONCAT(`prefix`,`datePrefix`,`id`) AS series, `name`, `age` FROM combinedKey;
+---------------+-------+------+                                                                                                                                                                                                      
| series        | name  | age  |                                                                                                                                                                                                      
+---------------+-------+------+                                                                                                                                                                                                      
| JTM0106160001 | Sally |   34 |                                                                                                                                                                                                      
+---------------+-------+------+                                                                                                                                                                                                      
1 row in set (0.00 sec)

If you need the new series number before inserting name, age, etc. you could insert with dummy data/null, store the inserted row/new series number, and UPDATE afterwards.

thinking about it: does the incrementing id has to restart with 0001 for each day? — Frederic Klein, Jun 02 '16 at 05:07

how to create automatic series number which are primary key for that table which has the following series JTM0605160001..... in SQL

1 Answers1