How to generate a student ID in PHP? And how to avoid goto?

Question

I want to generate a unique student ID. The format that I want is the last two digits of the current year plus 5 digits after that. For example: 2000001, 2000002, 2000003.. etc.. This is the extract of my code right now.

$pre = substr(strval(date("Y")),2);
$num = 1;
include "dbh.inc.php"; // this file merely creates the $conn variable that connects to mysql database.
$sql_cmd = "SELECT id FROM students WHERE id=?;";
$stmt = $conn->stmt_init();
if ($stmt) {
    $prepare = $stmt->prepare($sql_cmd);
    if ($prepare) {
        bind:
        $studentid = $pre . str_repeat('0', (4 - strlen($num)) ) . strval($num);
        $bind = $stmt->bind_param('s', $studentid);
        if ($bind) {
            $result = $stmt->execute();
            if ($result) {
                $num++;
                goto bind;
            }
            else {
                // insert student here using $studentid
            }
        }
    }
}

But I need to improve this because:

1. It uses a goto (I want to avoid it)
2. It seems overkill to prepare, bind, execute, etc everytime and query the database every loop.
3. It is obviously slow.

Please let me know if there is a better way of doing this.

Answer 1

You can generate new id on the MySQL side:

include "dbh.inc.php"; // this file merely creates the $conn variable that connects to mysql database.

$sql = 
    "SELECT CONCAT("
        . "DATE_FORMAT(CURDATE(), '%y'), "
        . "LPAD(COALESCE(MAX(RIGHT(id, 5)) + 1, 1), 5, '0')"
    . ") AS new_id "
    . "FROM students";

$result = $conn->query($sql);
if ($result) {
    if ($row = $result->fetch_assoc()) {
       // insert student here using $row['new_id']
    }
}

Or another option is to create an trigger on insert:

DELIMITER //

CREATE TRIGGER students_tr_bi BEFORE INSERT ON students
FOR EACH ROW
BEGIN

    SET NEW.id = (
        SELECT CONCAT(
            DATE_FORMAT(CURDATE(), '%y'),
            LPAD(COALESCE(MAX(RIGHT(id, 5)) + 1, 1), 5, '0')
        ) FROM students
    );
      
END//

DELIMITER ;

-- Usage:
INSERT INTO students (name) VALUES ('John');