Insert a record in two related tables

Question

Insert a record in two related tables

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I'm doing a project where I want to insert a record into two tables, where the user table has a primary key , and I have a client table > where there is a foreign key related to the primary key of the first one.

What I have tried to do is use two try catch , one to create the records for the user table, retrieve the id from the last record of the same, and insert the record of the customer table with the last record of user . The problem is that when I execute my code the second record in the second table. How can I do them: Here's the php code.

try {
   if ($conSQL) {
                $conSQL->beginTransaction();

                $sqlStatment = $conSQL->prepare(

                    "INSERT INTO usuario VALUES(
                    default,
                    :usuario,
                    :tipo,
                    :nombre,
                    :app,
                    :apm,
                    :contrasena)");

                $sqlStatment->bindParam(':usuario', $usuario);
                $sqlStatment->bindParam(':tipo', $tipo);
                $sqlStatment->bindParam(':nombre', $nombre);
                $sqlStatment->bindParam(':app', $app);
                $sqlStatment->bindParam(':apm', $apm);
                $sqlStatment->bindParam(':contrasena', $contrasena);

                //linea agregada
                $sqlStatment->execute();

                $lastInsertId = $conSQL->lastInsertId();

                $conSQL->commit();
                try {
                    if ($conSQL) {
                        $conSQL->beginTransaction();

                        $sqlStatment = $conSQL->prepare(

                            "INSERT INTO cliente VALUES(
                            default,
                            :idu,
                            :rfc,
                            :empresa,
                            :telefono,
                            :domicilio,
                            :contacto)");

                        $sqlStatment->bindParam(':idu', $lastInsertId);
                        $sqlStatment->bindParam(':rfc', $rfc);
                        $sqlStatment->bindParam(':empresa', $empresa);
                        $sqlStatment->bindParam(':telefono', $telefono);
                        $sqlStatment->bindParam(':domicilio', $domicilio);
                        $sqlStatment->bindParam(':contacto', $contacto);

                        //linea agregada
                        $sqlStatment->execute();



                        $conSQL->commit();
                    }

                        } catch (PDOException $e) {
                            $conSQL->rollBack();
                        }
            }

        } catch (PDOException $e) {
            $conSQL->rollBack();
       }

I thank you in advance for your great help. :)

mysql php

asked by JOSE ANGEL JIMENEZ CALDERON 13.02.2018 в 21:41

source

2 answers

Know which field is unique Make me get the total of adding and subtracting several jquery inputs

score 1 · Answer 1

To make the INSERT in both tables I advise you to do it atomic, with this I mean that:

You open a transaction.

You do the INSERT.

Recover id.

You make the other INSERT.

Finally you close the transaction.

In case there is an error, no value is saved, it is as if the INSERT had never been done, that is why you use the transactions. The use of try catch may be optional, but with this example I do not see it as necessary.

On the other hand, there must be a FOREIGN KEY as a restriction in both tables (client and user) and so forces that there is a parent record and you avoid registering orphans by code, situation that can occur if you use try catch, you must also define the FOREIGN KEY as NOT NULL to improve performance and avoid orphaned records at the data model level.

Finally, avoid using TRIGGERS as much as possible because you add complexity unnecessarily to this situation.

score 0 · Answer 2

I thank all those who supported me with their answers, but due to lack of experience and technique I set out to make an exclusive table for client . There was an exercise that I did and could do what I wanted, but I could not implement it in my code. I share and clarify that this code is not my complete authorship in case you know it or have seen it elsewhere.

Appointment in block

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "proyecto";
try {
 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $sql = "INSERT INTO usuario (usuario, tipo, nombre, app, apm, contrasena)
 VALUES ('c11', '3', 'luis', 'felipe', 'dabila', '123')";   
 $conn->exec($sql);
 $lastInsertId = $conn->lastInsertId();
 echo 'ultimo usuario insertado ' . $lastInsertId;
}
catch(PDOException $e)
{
 echo $sql . "<br>" . $e->getMessage();
 $conn = null;
 echo '<br>';
try {
 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $sql = "INSERT INTO cliente (idu,rfc,empresa,telefono,domicilio,contacto)
 VALUES ($lastInsertId,'abc000101a2c','torito','1234567890','andador 3 #3 colonia pedrgal','[email protected]')";   
 $conn->exec($sql);
 $last_id2 = $conn->lastInsertId();
 echo'y el ultimo cliente fue'.$last_id2;
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
}

I hope it helps you if you need it at some time. Luck and success to all.