Add new last number automatically mysql

Question

I have this id number in one column of my table,

0907003 0907004 0907005 1008005 1008006 1008007 1009001 1009002 1009003 

When I add the new value 1009 , it will add the last three numbers 004 , because the last number starts from 1009 (10-09) - 003 .

Or, if I add a new value 1008 , it will add the last three numbers 008
because the last number starts with 1008 (10-08) 007 .

Or, if I add a new value 0907 , it will add the last three numbers 006
because the last number starts with 0907 (09-07) 007 .

How to do it?

Thank you very much in advance!

 $front_id = $this->input->post('gameid'); //09 $middle_id = $this->input->post('netid'); //07 //$last_id will be generate automatically by sql $forID = $front_id.$middle_id; $sql = "INSERT INTO table ('colum') VALUES (".$forID.")" 

Answer 1

You need to insert a new identifier manually

  $max_of_letsay1009 = mysql_result(mysql_query("select MAX(id) from table where id like '1009%'"),0); // get the last 3 digits $new_number = (int)substr($max_of_letsay1009,-3) + 1; 

and you can also try:

  $new_id_of_letsay1009 = mysql_result(mysql_query("select MAX(id)+1 from table where id like '1009%'"),0); 

This is just my idea, not yet verified and not verified error

Answer 2

Try this request below

If your value is 1009

 SELECT MAX(RIGHT(ID,4))+1 FROM TableName WHERE LEFT(ID,4) = '1009' 

He will return the maximum number of this series.

Try this query for dynamic id length

 SELECT MAX(RIGHT(ID,len(id)-LEN('1009')))+1 FROM #TEMP WHERE LEFT(ID,LEN('1009')) = '1009' 

You can also use this query as an auxiliary query for the insert statement identifier column.

Answer 3

Maybe if it's not an Auto_Increment coulmn.

You just need to write logic about the insertion time.