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

993

I have a little tricky code, maybe you have a better solution:

What I want to do is, take the "id" of the last inserted entry from the database (1), then put two zeros infront of it (001), take the current "date" and format it (1506) and insert all together (1506001) again into the same row in my database into "orderID".

        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $sql1 = 'SELECT * FROM orders ORDER BY `date` DESC LIMIT 1';
        foreach ($pdo->query($sql1) as $row) {

          $dateID = date("ym", strtotime($row['date'])); 
          $id = sprintf("%03d", $row['id']);
          $orderID = $dateID.$id;

          $sql2 = "INSERT INTO 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 now is, that a new row is created. The value of "oderID" is not stored into the same row and I do not know how to achieve this.

701

Answer

Solution:

I assume after the first INSERT the field orderID is NULL.
You could therefore run a query that updates ALL recordsWHERE orderID IS NULL. No need to pull any values into PHP, MySQL's lpad(), date_format() and concat functions should do the 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 INTO soFoo (`name`, `date`) VALUES (:name, Now())');
$stmt->bindParam('name', $name);
foreach( array('John', 'Jane', 'Matt', 'Fred') as $name ) {
    $stmt->execute();
}

/* 'fix' orderIDs */
$pdo->exec("
    UPDATE
        soFoo
    SET
        orderID = concat(
            date_format(`date`, '%y%m'),
            lpad(`id`, 3, '0')
        )
    WHERE
        orderID IS NULL
");


/* print result */
foreach( $pdo->query('SELECT * FROM soFoo', PDO::FETCH_ASSOC) as $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 that still leaves the question what you will do once there are a thousand orders in that table.

650

Answer

Solution:

You need to useUPDATE instead ofINSERT:

    $sql2 = "UPDATE `orders` SET `orderID` = '$orderID' 
    WHERE 
        `id` = {$row['id']} 
    AND `date` ='{$row['date']}'
    AND `name` = '{$row['name']}' 
    AND `orderID` ='{$row['orderID']}'
    AND `orderID` != '$orderID'";

999

Answer

Solution:

All you need to do is:

Use mysql_insert_id() to get last inserted id
Create your new value for field orderID
Update your table for id you have got through mysql_insert_id()

People are also looking for solutions to the problem: How can I search value from Multidimensional array using PHP

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question