PHP MySQLi插入获取最后一列值加1

Question

I'm trying to run an INSERT query into a table with 3 columns. The first column is where I'm having the issue.

It is called COMM_CODE with VARCHAR value of 10 length, and is the primary key, ALLOW NULL is unchecked.

The values for COMM_CODE look like this:

 COMM_CODE
 c20188
 c20189
 c20190
 // and so on

What I would like to do, is when a new record is inserted, to basically add 1 to the most recent record.

Therefore, the most recent record is:

 c20190

So when I add a new record, the COMM_CODE for the new record will be:

 c20191

I tried this:

 INSERT INTO table_c 
   (COMM_CODE, COMM_DESC, DATE) 
 VALUES 
   (''+1, 'VIDEO GAMES', NOW());

But that just adds a number 1 to that column.

How can I make this happen?

Answer 1

Here the solution for your query : To generate the new code I'hv created get_new_code function in mysql. I hope you know how funcions work in mysql.

CREATE FUNCTION `get_new_code`() RETURNS varchar(11)
BEGIN 
    Declare var_code VARCHAR(11);
    SELECT max(`COMM_CODE`) INTO var_code FROM table_c;
    RETURN (CONCAT('c',(convert(substr(var_code,2,length(var_code)), SIGNED INTEGER)+1)));
END

Just to verify your logic you can use :

select get_new_code();

So that you will get the clear picture. Call this get_new_code function in insert query like this :

INSERT INTO 
`table_c`(`COMM_CODE`, `COMM_DESC`, `COMM_DATE`) 
VALUES 
(get_new_code(),'Description text',NOW());

This should solve your problem. :)