I need to insert into two tables at the same time. I have a user registry and I need the id of a user to be registered in two tables at the same time, thus making an association of this user with another table, which contains a group of users.
I need to insert into two tables at the same time. I have a user registry and I need the id of a user to be registered in two tables at the same time, thus making an association of this user with another table, which contains a group of users.
Can not multiply inserts
by statement
, but within transaction
yes:
BEGIN TRANSACTION
INSERT INTO Tabela1 (Coluna1, ...) VALUES (....);
INSERT INTO Tabela2 VALUES (..., LAST_INSERT_ID());
COMMIT
More about LAST_INSERT_ID ()
In the example above, everything is done by MySql. The LAST_INSERT_ID()
will get 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 value entered:
$ultimo_valor_inserido = mysql_insert_id();
With a little detail:
If your AUTO_INCREMENT column is a column of type BIGINT, the value returned by mysql_insert_id () is incorrect. Instead, use the MySQL internal SQL function LAST_INSERT_ID () in an SQL query.
For more information, see
I was able to insert into two tables doing so: I made the first inclusion using an IF, hence I checked if it was done with an IF, if so, then a new script is written inside the IF that makes a new insert. So:
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;
}
}