Set value of a foreign key with session

I’m trying to store a userid in session while he’s logging in, then get it from another page to save it in my database. For some reason, it says “could not able to execute insert into…” so I think it doesn’t get any value, but I don’t understand why. Here’s my login code:

if(password_verify($password, $hashed_password)){
session_start();
$_SESSION["loggedin"] = true;
$_SESSION["id"] = $id;
$_SESSION["username"] = $username;
$_SESSION["userid"] = $data[0]["userid"];  
//other stuff
}

and here’s my insert code:

session_start();
$link = mysqli_connect("localhost", "root", "", "reg");
mysqli_set_charset($link, "utf8");
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
//other variables which are working
$userid = mysqli_real_escape_string($link, $_SESSION['userid']);
echo var_dump($_SESSION);
$sql = "INSERT INTO cards (name, phone, phone2, email, zipcode, address, job, description, userid) VALUES ('$name','$phone', '$phone2', '$email', '$zipcode', '$address', '$job', '$description', '$userid')";

this is what I get with var_dump:

array(4) { ["loggedin"]=> bool(true) ["id"]=> int(1) ["username"]=> string(6) "admin3" ["userid"]=> NULL }

what’s the exact error message? your query is not even executed, that code is incomplete. enable error reporting

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Here’s the error message:

array(4) { ["loggedin"]=> bool(true) ["id"]=> int(1) ["username"]=> string(6) "admin3" ["userid"]=> NULL }
Fatal error: Uncaught mysqli_sql_exception: Cannot add or update a child row: a foreign key constraint fails (`reg`.`cards`, CONSTRAINT `fk_cards_userid` FOREIGN KEY (`userid`) REFERENCES `users` (`id`)) in C:\xampp\htdocs\vallalkozok-v2\insert.php:65 Stack trace: #0 C:\xampp\htdocs\vallalkozok-v2\insert.php(65): mysqli_query(Object(mysqli), 'INSERT INTO car...') #1 {main} thrown in C:\xampp\htdocs\vallalkozok-v2\insert.php on line 65

and here’s my full insert.php code:

<?php
session_start();
$link = mysqli_connect("localhost", "root", "", "reg");
mysqli_set_charset($link, "utf8");

 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Escape user inputs for security
$name = mysqli_real_escape_string($link, $_REQUEST['name']);
$job = mysqli_real_escape_string($link, $_REQUEST['job']);
$email = mysqli_real_escape_string($link, $_REQUEST['email']);
$phone = mysqli_real_escape_string($link, $_REQUEST['phone']);
$phone2 = mysqli_real_escape_string($link, $_REQUEST['phone2']);
$address = mysqli_real_escape_string($link, $_REQUEST['address']);
$description = mysqli_real_escape_string($link, $_REQUEST['description']);
$zipcode = mysqli_real_escape_string($link, $_REQUEST['zipcode']);
$userid = mysqli_real_escape_string($link, $_SESSION['userid']);
echo var_dump($_SESSION);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);


// attempt insert query execution
$sql = "INSERT INTO cards (name, phone, phone2, email, zipcode, address, job, description, userid) VALUES ('$name','$phone', '$phone2', '$email', '$zipcode', '$address', '$job', '$description', '$userid')";
if(mysqli_query($link, $sql)){
    header("Location: addbusiness.php?message=1");
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
                  

 
// close connection
mysqli_close($link);
?>

I used to use $_SESSION["userid"] = $userid; in my login.php to sace it’s data but get the same result with it. I think I should add something to my login code, but no idea what.

You are seeing that your userid session variable is NULL, aren’t you?

What is in $data[0]["userid"] when you set it?

That’s my problem, there is probably nothing because I don’t know how to set it. Here’s my login code:

<?php
// Initialize the session
session_start();
 

 
// Include config file
require_once "config.php";
 
// Define variables and initialize with empty values
$username = $password = "";
$username_err = $password_err = "";
 
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
 
    // Check if username is empty
    if(empty(trim($_POST["username"]))){
        $username_err = "Kérem adja meg a felhasználónevét.";
    } else{
        $username = trim($_POST["username"]);
    }
    
    // Check if password is empty
    if(empty(trim($_POST["password"]))){
        $password_err = "Kérem adja meg a jelszavát.";
    } else{
        $password = trim($_POST["password"]);
    }
    
    // Validate credentials
    if(empty($username_err) && empty($password_err)){
        // Prepare a select statement
        $sql = "SELECT id, username, password FROM users WHERE username = ?";
        
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "s", $param_username);
            
            // Set parameters
            $param_username = $username;
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                // Store result
                mysqli_stmt_store_result($stmt);
                
                // Check if username exists, if yes then verify password
                if(mysqli_stmt_num_rows($stmt) == 1){                    
                    // Bind result variables
                    mysqli_stmt_bind_result($stmt, $id, $username, $hashed_password);
                    if(mysqli_stmt_fetch($stmt)){
                        if(password_verify($password, $hashed_password)){
                            // Password is correct, so start a new session
                            session_start();
                            
                            // Store data in session variables
                            $_SESSION["loggedin"] = true;
                            $_SESSION["id"] = $id;
                            $_SESSION["username"] = $username;
                            $_SESSION["userid"] = $userid;
                                                  
                            
                            // Redirect user to welcome page
                            header("location: index.php");
                        } else{
                            // Display an error message if password is not valid
                            $password_err = "A megadott jelszó nem megfelelő.";
                        }
                    }
                } else{
                    // Display an error message if username doesn't exist
                    $username_err = "Érvénytelen felhasználónév.";
                }
            } else{
                echo "Hiba! Kérem próbálja meg később.";
            }
        }
        
        // Close statement
        mysqli_stmt_close($stmt);
    }
    
    // Close connection
    mysqli_close($link);
}
?>

I can’t see where your $userid variable comes from, you just assign it to a session variable and it hasn’t come from anywhere.

What’s the difference between the $id variable that you retrieve from your query, and the $userid variable that you are trying to use?

Yes that was my mistake. I shouldn’t use the userid variable there, I just need the id and set that in the insert.php to the userid, because that’s my foreign key there which uses the user’s id. The problem is, I don’t know how to add it to my insert.php. Could you show it please?

I’m not sure I follow what you are doing here. In your “insert” code, why can’t you just use the id sessions variable in place of where you were using the userid session variable?

You mean like adding this:

$userid = mysqli_real_escape_string($link, $_SESSION['id']);

and then this?

$sql = "INSERT INTO cards (name, phone, phone2, email, zipcode, address, job, description, userid) VALUES ('$name','$phone', '$phone2', '$email', '$zipcode', '$address', '$job', '$description', '$userid')";

because I’m still getting the same error this way aswell.

That was what I meant, yes. Presuming that the session variable does contain the value that you expect it to, does the query work if you run it with the same values in phpmyadmin or whatever direct admin tool you use? Is it something daft like putting quotes around the userid variable when it’s an integer field?

You should look at using a prepared statement for your insert, just like you do in the login code. You could probably lose the mysqli_real_escape_string calls and your quotes.

I just set it from escape string to prepared statements and everything is working with your suggestion by using the id session variable. Thanks mate.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.