Insert with Foreign Key (user + address (FK)) PHP (PDO) + MySQL

Question

Insert with Foreign Key (user + address (FK)) PHP (PDO) + MySQL

Navigation

#1 by (2 votes)

0

Good afternoon, guys.

I have a small question: When registering a client (with an address field (fk)) is the correct way something like this, or does it have a better way?

INSERT INTO endereco(logradouro, numero, bairro, cidade)
VALUES (:logradouro, :numero, :bairro, :cidade)");

Get the ID where the address matches what was previously inserted;

SELECT id FROM endereco
WHERE logradouro = :logradouro AND numero = :numero 
                 AND bairro = :bairro AND cidade = :cidade;

Insert the retrieved ID in SELECT in the FK (select_id);

INSERT INTO usuario(login, senha, nome, endereco_id)
VALUES (:login, :senha, :nome, :id_do_select)");

Is there any way to insert the address id into the client's FK without first doing a SELECT?

vlw.

php mysql pdo

asked by anonymous 30.11.2017 / 20:07

1 answer

PHP + JSON Webservice - Reading array BadRequest 400 HttpParams Angular 4.3

score 2 · Accepted Answer

If you are using php PDO

use the $ db-> lastInsertId ();

$sql = 'INSERT INTO 
endereco(logradouro, numero, bairro, cidade)
VALUES (:logradouro, :numero, :bairro, :cidade)';

$sql = $db->prepare($sql);
$sql->execute(array(valores-vao-aqui));

$id = $db->lastInsertId(); //retorna  ultimo  id inserido

Or by SQL

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

See how this function works in link