php - How can I create a value from two other values in the same table in my SQL?

Question

I have a table named "orders" in an SQL database:

id  date   name  orderID
========================
1   1502   John    ?
2   1502   Jane    ?

The "id" is set to AUTO_INCREMENT and the "date" is created when I insert data via my php form:

if ( !empty($_POST)) {
        $name = $_POST['name'];
        $valid = true;


        if ($valid) {
            $pdo = Database::connect();
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $sql = "INSERT INTO orders (name,date) values(?,CURDATE())";
            $q = $pdo->prepare($sql);
            $q->execute(array($name));
            Database::disconnect();
        }
}

I wish that the value for "orderID" is created automatically from the values "id" and "date":

id  date   name  orderID
========================
1   1502   John    15021
2   1502   Jane    15022

Answer 1

Due to the fact, that the id is an AUTO_INCREMENT you have no hand on this value. But it's quite simple. Add a AFTER INSERT trigger to the table and let it update the orderID column afterwards.

Here a quick example of a AFTER UPDATE trigger.

CREATE TRIGGER yourTrigger
AFTER INSERT
   ON yourTable FOR EACH ROW

BEGIN
    UPDATE yourTable
    SET orderid = CAST(date as char(4)) + CAST(id as char(1))
    WHERE ID = NEW.id

END;