null or empty foreign key in data insertion PHP

Question

I have a query regarding database and forms in PHP:

I have the following users table

CREATE TABLE IF NOT EXISTS `login`.`usuarios` (
    `nombres` VARCHAR(30) NULL,
    `apellidos` VARCHAR(30) NULL,
    `cedula` VARCHAR(13) NOT NULL,
    `telefono` VARCHAR(15) NULL,
    `direccion` VARCHAR(45) NULL,
    `sexo` VARCHAR(10) NULL,
    `sesion_id` INT(10) NOT NULL,
    PRIMARY KEY (`cedula`),
    INDEX `fk_usuarios_sesion_idx` (`sesion_id` ASC),
    CONSTRAINT `fk_usuarios_sesion`
    FOREIGN KEY (`sesion_id`)
        REFERENCES `login`.`sesion` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE)
ENGINE = InnoDB;

And I have the session table

CREATE TABLE IF NOT EXISTS `login`.`sesion` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `correo` VARCHAR(45) NULL,
    `usuario` VARCHAR(10) NULL,
    `password` VARCHAR(100) NULL,
    `last_session` DATETIME NULL,
    `activacion` INT NULL,
    `token` VARCHAR(40) NULL,
    `token_password` VARCHAR(100) NULL,
    `password_request` INT(11) NULL,
    `id_tipo` INT(11) NULL,
    `estatus` INT(2) NULL,
    PRIMARY KEY (`id`))
ENGINE = InnoDB;

They are related to each other from 1 to 1. The main session key is as a foreign key in users.

In PHP I have a form for users to register on my system. Everything is inserted normal in the tables but I only have one problem, because if in the sesions table the ID is autoincrement because being itself foreign in users is not filled too?

At this point I have already registered several users on my system, and everything inserts well, but the foreign key is always empty, it is not associated with the number that is assigned autoincrement in sessions.

To insert I do it in the following way:

function registraUsuario($nombres, $apellidos, $cedula, $telefono, $direccion, $sexo){

    global $mysqli;

    $stmtr= $mysqli->prepare("INSERT INTO usuarios (nombres, apellidos, cedula, telefono, direccion, sexo) VALUES(?,?,?,?,?,?)");
    $stmtr->bind_param('ssssss', $nombres, $apellidos, $cedula, $telefono, $direccion, $sexo);

    if ($stmtr->execute()){
        return true;
    } else {
        return 0;
    }

}

function registraSesion($email, $usuario, $pass_hash, $activo, $token, $tipo_usuario, $estatus){

    global $mysqli;

    $stmt = $mysqli->prepare("INSERT INTO sesion (correo, usuario, password, activacion, token, id_tipo, estatus) VALUES(?,?,?,?,?,?,?)");
    $stmt->bind_param('sssisii', $email, $usuario, $pass_hash, $activo, $token, $tipo_usuario, $estatus);

    if ($stmt->execute()){
        return $mysqli->insert_id;
    } else {
        return 0;   
    }   

}

Additional I must say that I have the foreign key as NULL because if I do not put it NULL, simply the data that registers for users (names, surnames, dni ..) are not registered ... but being NULL if everything is registered without inconveniences except The foreign key.

Please, if you can help me with this problem I have, or if you can tell me how I can do or what I am doing wrong?

Where do you try to update the users table with the session id? — Shadow, Jul 19 '17 at 03:21
@Shadow That's what i want to do I want to know how I do so that sesion_id that is foreign in the users table is updated according to the ID that it has as the primary key in table sesion. — rodrigo2324, Jul 19 '17 at 03:30
I would think that you would put a user id, into a session table. Users can never login from 2 devices. What if they forget to log out of one. That's it? Also a user can exist with no session, but can a session exist with no user. That will solve your problem by default. — ArtisticPhoenix, Jul 19 '17 at 03:33
Although, it may be simpler to place a unique index on the userid field within the sessions table. It is a lot more effective. — Shadow, Jul 19 '17 at 03:35
**WARNING**: Writing your own access control layer is not easy and there are many opportunities to get it severely wrong. Please, do not write your own authentication system when any modern [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) like [Laravel](http://laravel.com/) comes with a robust [authentication system](https://laravel.com/docs/5.4/authentication) built-in. At the absolute least follow [recommended security best practices](http://www.phptherightway.com/#security) and **never store passwords as plain-text**. — tadman, Jul 19 '17 at 04:33

score 1 · Answer 1 · answered Jul 19 '17 at 03:33

1

If only talking about the code, I think you must first register the session and then get the session id, and then do registering user.

But for traditional thinking, session table should have a foreign key from user table, but not the opposite.

answered Jul 19 '17 at 03:33

Yarco

763
9
18

I just said the same thing in the comments, about session being the dependent table. I think so too, seems weird otherwise. – ArtisticPhoenix Jul 19 '17 at 03:34
I think this is true - A user can exist with no session, but can a session exist with no user. Given this, there would never be a null ID to worry about. – ArtisticPhoenix Jul 19 '17 at 03:35
But it is still the same because if I pass the main key of users to the session table, the same is left null. My query is focused on how to make that foreign key be filled? I do not know how to write the sentence – rodrigo2324 Jul 19 '17 at 03:54
@rodrigo2324 That's the same thing as you write SQL to fill other fields. If the foreign key is `user_id` in session table, just add that field...ex.: `INSERT INTO session (user_id) VALUES ( ...LAST_ID_INSERT_FROM USER_TABLE...)` – Yarco Jul 19 '17 at 08:11

null or empty foreign key in data insertion PHP

1 Answers1