CODEWITHSUNDEEP
php
Marcelo Freitas
Marcelo Freitas

Reputation: 11

How to make insert in three tables OOP PHP + MySql

My question is how to insert three tables that are related as follows:

Customer table :

id
name
Address
id_serie ( fk )
id_telefone ( fk )

Table Serie :

description
id_serie ( pk )

table Telephone

number
id_telefone ( pk )

I am using PHP and OOP + html + bootstrap

My code to insert:

if ($valid) {
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO customers (name,address) values(?, ?, ?)";
    $q = $pdo->prepare($sql);
    $q->execute(array($name,$email,$mobile));
    Database::disconnect();
    header("Location: index.php");
}

If the columns only belong to a table I know what to do, with relationships, I'm not getting it.

Upvotes: 0

Views: 72

Answers (1)

Damien Genthial
Damien Genthial

Reputation: 64

If I understand correctly, you should first insert in series and telephone, then in customers. Something like that (if id_serie and id_telephone are autoincremented keys) :

if ($valid) {
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $q = $pdo->prepare("INSERT INTO series (description) values(?)");
    $q->execute(array("Mobile");
    $serie = $q->lastInsertId();
    $q = $pdo->prepare("INSERT INTO telephone (number) values(?)");
    $q->execute(array($mobile);
    $tel = $q->lastInsertId();

    $sql = "INSERT INTO customers (name,address, id_serie, id_telefone) values(?, ?, ?, ?)";
    $q = $pdo->prepare($sql);
    $q->execute(array($name,$email,$serie,$tel));
    Database::disconnect();
    header("Location: index.php");
}

You may also want to get an existing serie, then you have to make a fetch to get the correct id.

Upvotes: 1

Related Questions