Query Failed : SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

[fmax]

Dear All

I am using below 2 PHP functions to insert data to database. Its returning me " Query Failed : SQLSTATE[HY093]: Invalid parameter number: parameter was not defined " error. When i run the SQL in phpMyAdmin query box its working fine. can anyone advice me where i am wrong in PHP code

 

function getLoginId(object $pdo,string $LogID){

    $query = "SELECT * FROM MASTER_LOGIN;";
    $stmt = $pdo->prepare($query);
    $stmt->execute();
    $count = $stmt->rowCount();
    
    if($count == 0){
        $LogID = "LOG0000001";
        return $LogID;
    }
    else
    {
        $LogID = $count + 1;
        $LogID = str_pad((string)$LogID, 7,"0", STR_PAD_LEFT);
        $LogID = "LOG".$LogID;
        return $LogID;
    }
}

function set_user($pdo,$pUserName,$pPassword,$LogID){
    $LogID = getLoginId($pdo,$LogID);
    $query = "INSERT INTO MASTER_LOGIN (LOGIN_ID,LOGIN_USERNAME,LOGIN_PASSWORD) VALUES 
    (:LogID,:pUserName,:pPassword);";
    $stmt = $pdo->prepare($query);
    
    $options = [
        'cost' => 12
    ];
    $hashedPwd = password_hash($pPassword, PASSWORD_BCRYPT, $options);
    $stmt->bindParam(":LOGIN_ID",$LogID);
    $stmt->bindParam(":LOGIN_USERNAME",$pUserName);
    $stmt->bindParam(":LOGIN_PASSWORD",$hashedPwd);
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result;
}

 

[mac_gyver]

when using named prepared query place-holders, the name must match between what you use in the sql query and the binding. in your code, they don't. e.g. :LogID is not the same as :LOGIN_ID. ...

i recommend that you use simple positional ? place-holders. there's less to type and keep track of. regardless of the place-holder type, you can simply supply an array of the input values to the ->execute([...]) call, saving more typing.

you should not manage the id value yourself in code. this is not concurrent safe. instead, use an autoincrement primary index. the database engine will perform the necessary table locking to insure that concurrent queries will generate unique values.

your database must enforce uniqueness, it is the last step in the process. the username column must be defined as a unique index. you would then attempt to insert a row of data, and test in the exception handling catch block for that query if an duplicate index error (number) occurred. if it did, setup a message for the user letting them know that the username is already in use. for all other error numbers, rethrow the exception and let php handle it.

there's also nothing to fetch from an insert query. why are you doing that?

[fmax]

function getUserName(object $pdo, string $pUserName){

    $query = "SELECT LOGIN_USERNAME FROM MASTER_LOGIN WHERE LOGIN_USERNAME = :pUserName;";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(":pUserName",$pUserName);
    $stmt->execute();

    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result;
}

function username_exists(object $pdo, string $pUserName){
    if(getUserName($pdo, $pUserName)) {
        return true;
    } 
    else {
        return false;
    }
}

if (username_exists($pdo, $pUserName)){
            $errors["username_exists"] = "Username Already Exists";
        }

3 hours ago, mac_gyver said:

when using named prepared query place-holders, the name must match between what you use in the sql query and the binding. in your code, they don't. e.g. :LogID is not the same as :LOGIN_ID. ...

My bad i didn't check before posting

i recommend that you use simple positional ? place-holders. there's less to type and keep track of. regardless of the place-holder type, you can simply supply an array of the input values to the ->execute([...]) call, saving more typing.

you should not manage the id value yourself in code. this is not concurrent safe. instead, use an autoincrement primary index. the database engine will perform the necessary table locking to insure that concurrent queries will generate unique values.

your database must enforce uniqueness, it is the last step in the process. the username column must be defined as a unique index. you would then attempt to insert a row of data, and test in the exception handling catch block for that query if an duplicate index error (number) occurred. if it did, setup a message for the user letting them know that the username is already in use. for all other error numbers, rethrow the exception and let php handle it.

Yes i am doing this using

 

there's also nothing to fetch from an insert query. why are you doing that?