Problem trying to insert records with foreign keys in php fat free framework and mysql-CodePudding

I need to insert the record in the usuarios table, then in the personas table using the foreign key id_usuario and finally insert the record in the usuarios_roles table using the foreign key id_usuario as well. I am working with PHP Fat Free Framework. This is my form to register a persona in my view record.html (please ignore blank inputs):

<form action="registroEmpresa" method="post">
    <input type="text"  name="rut" />
    <input type="text"  name="nombre_razonsocial" />
    <input type="text"  name="telefono" />
    <input type="email"  name="email" />
    <input type="password"  name="password" />
    <?php $empty = ' '; ?>
    <input id="" name="apellidos" type="hidden" value="<?php echo $empty; ?>">
    <input id="" name="direccion" type="hidden" value="<?php echo $empty; ?>">
    <input id="" name="pais" type="hidden" value="<?php echo $empty; ?>">
    <input id="" name="region" type="hidden" value="<?php echo $empty; ?>">
    <input id="" name="ciudad" type="hidden" value="<?php echo $empty; ?>">
     <input id="" name="comuna" type="hidden" value="<?php echo $empty; ?>">
    <button type="submit">Registrarme</button>
</form>

I must follow this relational model that connects the table personas with the table usuarios and in turn usuarios_roles. Primary keys are all self-incremental: ( Relational MySQL Model)

Now comes the part that I don't understand how to program. At the time of doing the save() I can register a user, but when I do the save() of persona obviously it will not be linked to the id_usuario and there will not be an INSERT in the table usuarios_roles. Currently I receive the information in my index.php specifically here:

$f3->route('POST @registroEmpresa: /registroEmpresa',
function($f3) {
    $db = getConnection(); 

    $usuario = new DB\SQL\Mapper($db, 'usuarios'); 
    $usuario->copyFrom('POST');
    $usuario->save();   //insert new usuario row    
    
    $persona = new DB\SQL\Mapper($db, 'personas'); 
    $persona->copyFrom('POST');
    $persona->save();   //insert new persona row (without foreign key ;C  )   

    //get credentials
    $email = $f3->get('POST.email');
    $password = $f3->get('POST.password'); 

    //bad practices to add "id_usuario" to personas table
    $db->exec("UPDATE `personas` 
               SET `id_usuario` = (SELECT `id_usuario` FROM `usuarios`
                                    WHERE `email` = '$email' and `password` = '$password')
               ORDER BY `id_persona` DESC
               LIMIT 1"); 

    //bad practices to add "id_usuario" to usuarios_roles table
    $db->exec("INSERT INTO `usuarios_roles`(`id_usuario`, `id_rol`) VALUES
             (( SELECT `id_usuario` FROM `usuarios`
                WHERE `email` = '$email' and `password` = '$password'ORDER BY `id_usuario` DESC
               LIMIT 1),5)"); 

    echo View::instance()->render('login.html');
});

Do you have any PHP fat-free code or idea that can serve me for this purpose, without using updates, inserts, or SQL queries? Thanks

CodePudding user response：

What you might be after is available in a plugin called f3-cortex. In there, there's a specific section about relations that may be of benefit to what you're trying to accomplish. https://github.com/ikkez/f3-cortex#relations There are 1:1, 1:many, many:many relationships that you can set up that will take care of what you need.

With Cortex you can create associations between multiple models. By linking them together, you can create all common relationships you need for smart and easy persistence.