PDO - Fatal Error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row:

Question

I know that there are already questions exist with the same problem that I have, but it doesn't help me fix the problem on what I'm having in my PDO.

So, I want to ask for help on how can I fix the code and what can I do to fix it.

What I'm trying to do was to insert userlv(field) using my create.php by creating an account with fields (username),(password),(userlv) into my Table[users], but it won't let me, which I get this error instead.

(I know that I have userlv from Table[accesslvl] and userlv from Table[users] making userlv-Table[accesslvl] as Unique Key and userlv-Table[users] as Index making them in a relationship).

Also doing the INSERT INTO users (username, passw, userlv) VALUES ('username', 'passw', 'SUPERADMIN') in phpmyadmin works fine and adds values, but when doing INSERT INTO users (username, passw, userlv) VALUES (:username, :passw, :userlv) in create.php the error appears.

Also when removing the userlv entirely from create.php leaving the fields (username and passw) it will successful create the account.

So yeah, what can I do, any solutions? I can include the other codes if it's needed. Thanks in advance.

---

The ERROR

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 
1452 Cannot add or update a child row: a foreign key constraint fails (`ces`.`users`, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`userlv`) REFERENCES `accesslvl` (`userlv`) ON UPDATE CASCADE) in C:\xampp\htdocs\includes\create-inc.php:87 
Stack trace: #0 C:\xampp\htdocs\includes\create-inc.php(87): PDOStatement->execute() #1 C:\xampp\htdocs\create.php(2): include('C:\\xampp\\htdocs...') #2 {main} thrown in C:\xampp\htdocs\includes\create-inc.php on line 87

The CODE create-inc.php

<?php
// Include config.php file
require_once "includes/config.php";
 
// Define variables and initialize with empty values
$username = $passw = $repeat_passw = $userlv = "";
$username_err = $passw_err = $repeat_passw_err = $userlv_err = "";
 
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
 
    // Validate username
    if( empty(trim($_POST["username"])) ){
        $username_err = "Please enter a username.";
    } else{
        // Prepare a select statement
        $sql = "SELECT uid FROM users WHERE username = :username";
        
        if($stmt = $pdo->prepare($sql)){
            // Bind variables to the prepared statement as parameters
            $stmt->bindParam(":username", $param_username, PDO::PARAM_STR);
            
            // Set parameters
            $param_username = trim($_POST["username"]);
            
            // Attempt to execute the prepared statement
            if($stmt->execute()){
                if($stmt->rowCount() == 1){
                    $username_err = "This username is already taken.";
                } else{
                    $username = trim($_POST["username"]);
                }
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }

            // Close statement
            unset($stmt);
        }
    }
    
    // Validate password
    if(empty(trim($_POST["passw"]))){
        $passw_err = "Please enter a password.";     
    } elseif(strlen(trim($_POST["passw"])) < 12){
        $passw_err = "Password must have atleast 12 characters.";
    } else{
        $passw = trim($_POST["passw"]);
    }
    
    // Validate confirm password
    if(empty(trim($_POST["repeat_passw"]))){
        $repeat_passw_err = "Please confirm password.";     
    } else{
        $repeat_passw = trim($_POST["repeat_passw"]);
        if(empty($passw_err) && ($passw != $repeat_passw)){
            $repeat_passw_err = "Password did not match.";
        }
    }
    
        // Validate userlv
    if(empty(trim($_POST["userlv"]))){
        $userlv_err = "Please enter a userlv.";     
    } else{
        $userlv = trim($_POST["userlv"]);
    }
    
    // Check input errors before inserting in database
    if(empty($username_err) && empty($passw_err) && empty($repeat_passw_err) && empty($userlv_err)){
        
        // Prepare an insert statement
        $sql = "INSERT INTO users (username, passw, userlv) VALUES (:username, :passw, :userlv)";
        
         
        if($stmt = $pdo->prepare($sql)){
            // Bind variables to the prepared statement as parameters
            $stmt->bindParam(":username", $param_username, PDO::PARAM_STR);
            $stmt->bindParam(":passw", $param_passw, PDO::PARAM_STR);
            $stmt->bindParam(":userlv", $param_userlv, PDO::PARAM_STR);
            
            // Set parameters
            $param_username = $username;
            $param_passw = password_hash($passw, PASSWORD_DEFAULT); // Creates a passw hash
            $param_userlv = $userlv;
            
            // Attempt to execute the prepared statement
            if($stmt->execute()){
                // Redirect to login page
                header("location: login");
            } else{
                echo "Something went wrong. Please try again later.";
            }

            // Close statement
            unset($stmt);
        }
    }
    
    // Close connection
    unset($pdo);
}
?>

Answer 1

Instead of

$stmt->bindParam(":username", $param_username, PDO::PARAM_STR);
$stmt->bindParam(":passw", $param_passw, PDO::PARAM_STR);
$stmt->bindParam(":userlv", $param_userlv, PDO::PARAM_STR);

Create an associative array and insert values in it then execute it and see if that works

$paramArray = array (
     'username' => $username,
     'passw' => password_hash($passw, PASSWORD_DEFAULT), // Creates a passw hash
     'userlv' => $userlv );
$stmt->execute($paramArray);
$stmt->closeCursor();