[Solved] How to Create an auto-increment value on MySQL using a text field? [closed]

Question

There are many things to solve the problem. Let me show you one of them by using MySQL procedures.

Steps are as follows:

1°) create a procedure that generates an identifier for a table (generate_id)

2°) create a procedure that inserts the data into the table (insert_users) by using the first procedure (generate_id) to get a formatted ID, then it will return the inserted ID as a SELECT query.

3°) Now, call the inserting procedure (insert_users)

For more information, chat with Michel Magloire Ekanga who is the main creator of this craftiness.

LET’S USE AN EXAMPLE FOR EACH STEP

STEP 1

generate_id procedure that should take as parameters: table_name, the primary_key, a joiner or a prefix for ID, the length for ID, and the output

Our result should be as below: USER20210909000002 (joiner=USER, year=2021, mounth=09, day=09, increment=000002)

# ---------------------   DEFINITION ------------------------------
DROP PROCEDURE IF EXISTS generate_id;
DELIMITER $$

CREATE PROCEDURE generate_id(IN _db_table VARCHAR(255), IN _pkey VARCHAR(255),IN _joiner VARCHAR(255),_length INT, OUT _new_id VARCHAR(255))
BEGIN
    SET @max_id = NULL;
    SET @sql = CONCAT('select max(`', _pkey, '`)  into @max_id from `', _db_table, '`');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    #------- Length for identifier -------
    SET @length = 10;
    SET @joiner_len = CHAR_LENGTH(_joiner);
    IF _length > 0 AND (_length - CHAR_LENGTH(_joiner)) >= 10 THEN
    SET @length = _length;
    END IF;
    #----------------------------
    #--- the date variables ---
    SET @today = DATE_FORMAT(NOW(),'%Y%m%d');
    SET @r_str = LPAD(1,(@length - (8 + @joiner_len)), '0');
    #
    #---- FORMATING ID ------------------------
    #
    IF @max_id IS NULL THEN 
    # the table is empty
    SET _new_id = CONCAT(_joiner,@today,@r_str);
    ELSE 
    # the table is not empty
    # 1°) reading parts from previous ID 
    SET @strlen = CHAR_LENGTH(@max_id);
    SET @old_r_str = SUBSTR(@max_id, (@joiner_len + 4 + 2 + 2 + 1), @strlen);
    SET @old_idx = CONVERT(@old_r_str, SIGNED INTEGER);
    # 2°) checking if dates are the same
    SET @old_date = SUBSTR(@max_id, (@joiner_len + 1), 8);
    -- dates are not the same, we just take the 8 characters for date from the field
    SET @new_idx = 1;
    SET @new_r_str = LPAD(@new_idx,(@length - (8 + @joiner_len)), '0');
    SET @new_max = CONCAT(_joiner,@today,@new_r_str);
    IF @today = @old_date THEN
        SET @new_idx = @old_idx + 1;
        SET @new_r_str = LPAD(@new_idx,(@length - (8 + @joiner_len)), '0');
        SET @new_max = CONCAT(_joiner,@old_date,@new_r_str);
    END IF;
    SET _new_id =  @new_max;
    END IF;
END;
$$

DELIMITER ;

STEP 2

Procedure that uses the generated ID for insertion, we don’t need to provide an ID, it will generate the ID automaticaly

DROP PROCEDURE IF EXISTS insert_users;
DELIMITER $$

CREATE PROCEDURE insert_users(
    login_user VARCHAR(255),
    pass_user VARCHAR(255)
)
BEGIN
    CALL generate_id('users', 'row_id', 'USR', 22, @new_id);
    SET @last_inserted_id = @new_id;
    SET @sql = CONCAT("INSERT INTO users(row_id, login_user, pass_user) VALUES ('", 
        @last_inserted_id, "','",
        login_user, "','",
        pass_user, "')"
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    # ---- export the new ID before exiting the function
    SELECT @last_inserted_id AS lastInsertId;

END;
$$

DELIMITER ;

STEP 3

How to use with PHP, for example

<?php

function insert(){
    $db = $pdo; // I am using PDO as driver, renewed as Class
    $lastInsert = null;
    //---------------
    $sql = "CALL insert_users(?,?)";
    //---------------
    
    $req = $db->prepare($sql);
    $req->execute($login_user,$pass_user);
    if($req->rowCount() > 0){
        $lastInsert = $req->fetch()->lastInsertId;
        return true;
    }else{
        return false;
    }
}

?>

Accepted Answer