Make Insert in two tables at the same time

Asked

Viewed 13,066 times

2

I need to do an Insert on two tables at once. I have a user registration and I need a user id to be registered in two tables at the same time, thus making an association of this user to another table, which contains a group of users.

mysql database

2 answers

5


It is not possible to do multiple inserts through a statement, but within a transaction yes:

BEGIN TRANSACTION
   INSERT INTO Tabela1 (Coluna1, ...) VALUES (....);
   INSERT INTO Tabela2 VALUES (..., LAST_INSERT_ID());
COMMIT

More about the LAST_INSERT_ID()

In the above example, everything is done by Mysql. The LAST_INSERT_ID() will take the value of the column that is autoincrement in the first Insert. If you want to use PHP, you can do:

Insert into ....

By PHP, you can get the last entered value:

$ultimo_valor_inserido = mysql_insert_id();

With one small detail:

If your AUTO_INCREMENT column is a BIGINT column, the value returned by mysql_insert_id() will be incorrect. Instead, use the internal Mysql SQL function LAST_INSERT_ID() in an SQL query.

For more information, consult

  • This is with php, right?

  • Can you tell me more about your answer?

  • Friend, I edited the answer... Everything is done by Mysql, but you can also use the same artificially via PHP to be able to enter the last record. Take a look at my edited answer, if in doubt, ask again.

-1

I was able to insert it into two tables like this: I made the first inclusion using an IF, then I checked if it was done with an IF, if yes, then a new script is written inside the IF that makes a new insertion. Thus:

   public function addUsuario($usuarios_nome, $usuarios_email, $usuarios_senha, $usuarios_permissoes, $usuarios_imagem = null){     

    if ($this->existeEmail($usuarios_email) == false) {
                    $sql = "INSERT INTO usuarios (usuarios_nome, usuarios_email, usuarios_senha, usuarios_permissoes, usuarios_imagem) VALUES (?, ?, ?, ?, ?)";
                    $sql = Conexao::getInstance()->prepare($sql);
                    $sql -> bindValue(1, $usuarios_nome);
                    $sql -> bindValue(2, $usuarios_email);
                    $sql -> bindValue(3, $usuarios_senha);
                    $sql -> bindValue(4, $usuarios_permissoes);
                    $sql -> bindValue(5, $usuarios_imagem);                                  
                    //$sql -> execute();
                            if($sql->execute()){
                                    $sql = "INSERT INTO colaboradores (colaboradores_usuarios_id, colaboradores_status) VALUES (LAST_INSERT_ID(), 1)";
                                    $sql = Conexao::getInstance()->prepare($sql);
                                    $sql -> execute();                                                                  
                            };
                                          return true;

    }else{
        return false;
    }           

   }

Browser other questions tagged mysql database

You are not signed in. Login or sign up in order to post.