CODEWITHSUNDEEP
phpmysql
iyal
iyal

Reputation: 1285

Add new last number automatically mysql

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.")"

Upvotes: 6

Views: 309

Answers (3)

Rajasekar Gunasekaran
Rajasekar Gunasekaran

Reputation: 1827

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.

Upvotes: 1

Eng Cy
Eng Cy

Reputation: 1557

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

Upvotes: 6

user4498206
user4498206

Reputation:

It is possible if it is not Auto_Increment coulmn.

Just need to write logic on insert time.

Upvotes: 0

Related Questions