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 new value of 1009, it will add last three number of 004 because the last number begin with 1009 (10-09) is 003.

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

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

How to do this?

Many thanks 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 have to insert new id 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;

or you can try this too:

    $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 tested and no error checking

Answer 2

You try this below query

If your value is 1009

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

It will return the max number of that 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 sub query for the insert statement's ID column.