0

I am trying to add a feature in my system that automatically generates Student ID in YYYY-0000 format for newly enrolled students. YYYY is the year when the student got enrolled while 0000 is 4 integer data type. How do I code this one? Appreciate your answers.

Temani Afif
  • 245,468
  • 26
  • 309
  • 415
Jhon Louie Rubin
  • 1
  • 2
  • Do you ever need to add new students from previous years, or once 2023 starts do you start at 20230001 and just count up from there until 2024? – Greg Schmidt Apr 19 '23 at 03:19
  • 1
    Please include snippet of code or error that you have faced. Please also check [Guide on asking questions](https://stackoverflow.com/help/how-to-ask) – Anant V Apr 19 '23 at 03:21
  • 1
    This is a bad idea. Just let your database generate an auto-increment ID for the student, and put the year they registered in another field in the same dataase row. If need be, you can then construct this formatted "ID" in a SELECT query when reading the data back. Trying to make the ID via script is inefficient and also runs a strong risk of generating duplicates IDs for more than one student. – ADyson Apr 19 '23 at 07:26
  • Related: [Auto Increment including Year and Month in MySql](https://stackoverflow.com/q/14828807/2943403) and [reset auto-increment each year with new prefix](https://stackoverflow.com/q/41552237/2943403) and [MYSQL autoincrement with date day year](https://stackoverflow.com/q/64494713/2943403) and [MySQL Field AUTO_INCREMENT according to YEAR](https://stackoverflow.com/q/30727435/2943403) and [Auto-increment - automatic reset for each year](https://stackoverflow.com/q/30167587/2943403) – mickmackusa Apr 19 '23 at 11:33

1 Answers1

0

Here is an example code in bash. Converting it to php should not be an issue:

#!/bin/bash

mysql -t test << __EOT__

#--- drop and create a table
drop table if exists test;
create table test
(
    id int unsigned auto_increment primary key,
    name varchar(10)
);

#--- insert element. Before set AUTO_INCREMENT 
#--- to the first value of the current year

ALTER TABLE test AUTO_INCREMENT=20210001;
insert into test set name="2021.1";

ALTER TABLE test AUTO_INCREMENT=20210001;
insert into test set name="2021.2";

ALTER TABLE test AUTO_INCREMENT=20230001;
insert into test set name="2023.1";

ALTER TABLE test AUTO_INCREMENT=20230001;
insert into test set name="2023.2";

select * from test;

__EOT__

The id is year*10000+index. After setting AUTO_INCREMENT, the db system search for the highest id and add 1 for the next inserted record. Here you can't add students of the previous year.

The output is:

+----------+--------+
| id       | name   |
+----------+--------+
| 20210001 | 2021.1 |
| 20210002 | 2021.2 |
| 20230001 | 2023.1 |
| 20230002 | 2023.2 |
+----------+--------+
Wiimm
  • 2,971
  • 1
  • 15
  • 25
  • [The fundamental goal of closing duplicate questions is to help people find the right answer by getting all of those answers in one place.](https://stackoverflow.com/help/duplicates#:~:text=The%20fundamental%20goal%20of%20closing%20duplicate%20questions%20is%20to%20help%20people%20find%20the%20right%20answer%20by%20getting%20all%20of%20those%20answers%20in%20one%20place.) – mickmackusa Apr 19 '23 at 11:27