php - How can I insert a value into the last database entry? -

i have little tricky code, maybe have better solution:

what want is, take "id" of last inserted entry database (1), put 2 zeros infront of (001), take current "date" , format (1506) , insert (1506001) again same row in database "orderid".

        $pdo = database::connect();         $pdo->setattribute(pdo::attr_errmode, pdo::errmode_exception);          $sql1 = 'select * orders order `date` desc limit 1';         foreach ($pdo->query($sql1) $row) {            $dateid = date("ym", strtotime($row['date']));            $id = sprintf("%03d", $row['id']);           $orderid = $dateid.$id;            $sql2 = "insert orders (orderid) values('$orderid') ";           $q2 = $pdo->prepare($sql2);           $q2->execute();            database::disconnect();         } 

for example:

id  date   name    orderid ========================== 1   1505   john    1505001 2   1505   jane    1505002 1   1506   mad     1506001 2   1506   fred    1506002 

what happens is, new row created. value of "oderid" not stored same row , not know how achieve this.

i assume after first insert field orderid null.
therefore run query updates records where orderid null. no need pull values php, mysql's lpad(), date_format() , concat functions should trick.

sscce:

<?php $pdo = new pdo('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(     pdo::mysql_attr_direct_query => false,     pdo::attr_errmode=>pdo::errmode_exception )); setup($pdo);  /* insert new orders */ $stmt = $pdo->prepare('insert sofoo (`name`, `date`) values (:name, now())'); $stmt->bindparam('name', $name); foreach( array('john', 'jane', 'matt', 'fred') $name ) {     $stmt->execute(); }  /* 'fix' orderids */ $pdo->exec("     update         sofoo     set         orderid = concat(             date_format(`date`, '%y%m'),             lpad(`id`, 3, '0')         )             orderid null ");   /* print result */ foreach( $pdo->query('select * sofoo', pdo::fetch_assoc) $r ) {     echo join(', ', $r), "\r\n"; }   function setup($pdo) {     $pdo->exec('         create temporary table sofoo (             `id` int auto_increment,             `date` date,             `name` varchar(32),             `orderid` varchar(32),             primary key(id)         )'     ); } 

prints (today):

1, 2015-06-19, john, 1506001 2, 2015-06-19, jane, 1506002 3, 2015-06-19, matt, 1506003 4, 2015-06-19, fred, 1506004 

...but still leaves question once there thousand orders in table.