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

Asked

Viewed 662 times

0

Good afternoon, you guys.

I have a small question: When registering a customer (with an address field (fk)) the correct way is something like this, or has 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 entered;

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

Insert the recovered ID into SELECT in FK(id_do_select);

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

Have any way to enter the address id in the client’s FK without first making a SELECT?

vlw.

php mysql pdo

  • Address has primary key?

1 answer

2


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 it works in http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-Insert-id

Browser other questions tagged php mysql pdo

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