What is the best practice for running select after PHP Insert?

Question

After making an insert in the database, I return the newly created id and then make a get of this same id to perform another function. But as it is all very fast, the get comes before you have all Insert done. I am developing with slim framework 2 and mysql. As it is a webservice and will have several requests of these, I think using usleep() is not the right way. What is good practice in this situation?

As a suggested edition of colleagues, translation into code: I’m doing it in the slim framework, so I do a post to insert the data

    $app->post('/cria', function() use ($app) {

            $id = (new \controllers())->criaEvento();
            if(isset($id)
              $app->applyHook('notifica.usuarios', $id);
        });

Here I do the Insert and return last id inserted. After there is a hook for the application to notify some users where will have a logic for which of them

function criaEvento()
    {
        try {

            $stmt = $this->conn->prepare("INSERT INTO Tabela");
            $stmt->execute();

            $result = $this->conn->lastInsertId();

            return $result;

        } catch(\PDOException $e){
            echo $e->getMessage();
        }
    }

Here would be the hook, but you can already exemplify, because I’m planning the logic

$app->hook('notifica.usuario', function ($id) use ($app) {
    $eventos = (new \controllers())->getEvento($id);
// aqui retorna que nao foi possivel retornar as colunas.
});

The Insert is done, but apparently the get is faster

Answer 1

A 'good practice' would be to not execute a select after to request the data that has just been entered (unless there is a need to fetch some data generated by the database at the time of creation of the record or other records).

A possible option, would be to use some object resource for interaction with the database, I will try to exemplify in this answer:

Let’s create two an object reference to the table, in the example we will have a table users:

This would be the user object:

class Users extends Base{

    public $nome;

    public $email;

    public function __construct($nome, $email)
    {
        $this->nome = $nome;
        $this->email = $email;
    }

}

And class Base, from which the object will be extended:

class Base {

    public $id;

    public function get()
    {
        $this;
    }

    public function getTablename()
    {
        return strtolower(get_class($this));
    }

    public function getParameters()
    {
        $arr = array_keys($this->getArray());
        $response = [];
        foreach ($arr as $item){
            $response[] = '?';
        }
        return implode(', ', $response);
    }

    public function getColumns()
    {
        return (implode(',', array_keys($this->getArray())));
    }

    public function getArray()
    {
        $arr = (new ReflectionObject($this))->getProperties(ReflectionProperty::IS_PUBLIC);

        $array_response = [];

        foreach($arr as $item){
            if(!is_null($this->{$item->name})){
                $array_response[$item->name] = $this->{$item->name};
            }
        }
        return $array_response;
    }

    public function __toString()
    {
        return json_encode($this);
    }

}

That class Base, will be responsible for converting your User object into a query.

After that, create a third class to connect and interact with your database:

class DBUtil {

    private $host;

    private $dbname;

    private $user;

    private $password;

    public $pdo;

    public function __construct()
    {
        $this->host = '127.0.0.1';
        $this->dbname = 'stack';
        $this->user = 'root';
        $this->password = 'root';
        try {

            $this->pdo = new PDO('mysql:host=' . $this->host . ';dbname=' . $this->dbname, $this->user, $this->password);
        }catch (PDOException $e){
            var_dump($e->getMessage());
        }
    }

    public function insert(Base $object)
    {

        $query = "INSERT INTO  {$object->getTablename()}  ( {$object->getColumns()} ) VALUES (  {$object->getParameters()} )";
        $stmt = $this->pdo->prepare($query);

        try {
            $this->exec($stmt, $object);

        }catch (PDOException $e){
            $this->pdo->rollBack();
            var_dump($e->getMessage());
            return null;
        }

        $object->id = $this->pdo->lastInsertId();

        return $object;
    }

    public function exec($stmt,Base $object)
    {
        $stmt->execute(
            array_values($object->getArray())
        );
    }
}

This third class will connect to the database through the PDO, and note that in the method insert we use the Base class to generate a query of the specified object .

How this can solve your problem ?

First, instead of writing an insert query, you will create an object from the User class,

$user = new Users($nome='Nome Da Silva',$email='[email protected]');

Start the connection class:

$db = new DBUtil();

With the reference of User in $user, and Dbutil in $db, call:

$user = $db->insert($user);
var_dump($user);

The Insert method, will use the User object inherited from Base to build the query, and pass to the PDO, and will return the same object inserted, now with the attribute $id with the call value of lastInsertId made to the PDO.

var_dump will print the object you need, without making a new select.

An example of something like this is the Eloquent of the Laravel.