php - 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.

标签： phpmysqli

Answer 1

您可以在 MySQL 端生成新的 id：

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']
    }
}

或者另一种选择是在插入时创建触发器：

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');