Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number

[Adamhumbug]

Hi All,

I have a very simple function:

function createNewUser($pdo, $fname, $lname, $email, $password)
{
	$sql = "SELECT email from user where email = :email";
	$stmt = $pdo->prepare($sql);
	$stmt->execute([
		':email' => $email
	]);
	$emailExists = $stmt->rowCount();

	if ($emailExists > 0) {
		return "This email address is already in use";
	}


	$sql2 = "INSERT INTO user (fname, lname, email, password) VALUES (':fname', ':lname', ':email', ':password')";
	$hashedPwd = password_hash($password, PASSWORD_BCRYPT);
	$stmt = $pdo->prepare($sql2);
	$stmt->execute([
		':fname' => $fname,
		':lname' => $lname,
		':email' => $email,
		':password' => $hashedPwd
	]);
	return 'User Created';
}

The first query is running fine, but the second is giving me

Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number (on the SQL2 line)

Can anyone shed any light on this?

[Adamhumbug]

Quotes around VALUES 🤦‍♂️

[Barand]

I wouldn't waste time on the first SELECT query.

Place a UNIQUE key constraint on the email column then

function createNewUser($pdo, $fname, $lname, $email, $password)
{
    try {
        $sql2 = "INSERT INTO user (fname, lname, email, password) VALUES (:fname, :lname, :email, :password)";
        $hashedPwd = password_hash($password, PASSWORD_BCRYPT);
        $stmt = $pdo->prepare($sql2);
        $stmt->execute([
            ':fname' => $fname,
            ':lname' => $lname,
            ':email' => $email,
            ':password' => $hashedPwd
        ]);
        return 'User Created';
    }
    catch (PDOException $e) {
        if ( str_contains($e->getMessage(), '1062 Duplicate entry')) {
            return "That email already exists";
        }
        else throw $e;
    }
}

 

[Adamhumbug]

10 hours ago, Barand said:

I wouldn't waste time on the first SELECT query.

Place a UNIQUE key constraint on the email column then

function createNewUser($pdo, $fname, $lname, $email, $password)
{
    try {
        $sql2 = "INSERT INTO user (fname, lname, email, password) VALUES (:fname, :lname, :email, :password)";
        $hashedPwd = password_hash($password, PASSWORD_BCRYPT);
        $stmt = $pdo->prepare($sql2);
        $stmt->execute([
            ':fname' => $fname,
            ':lname' => $lname,
            ':email' => $email,
            ':password' => $hashedPwd
        ]);
        return 'User Created';
    }
    catch (PDOException $e) {
        if ( str_contains($e->getMessage(), '1062 Duplicate entry')) {
            return "That email already exists";
        }
        else throw $e;
    }
}

 

OK, will give that a try.

 

Is this how i should be structuring all of my queries to interact with the database?

I have never used try / catch / throw

[Barand]

52 minutes ago, Adamhumbug said:

Is this how i should be structuring all of my queries to interact with the database?

Only when you need to report an error that the user can do something about (like duplicates).

Most DB errors are beyond user control so I don't bother with try..catch and just let php handle the error

[mac_gyver]

2 hours ago, Adamhumbug said:

Is this how i should be structuring all of my queries to interact with the database?

or you could read the detailed reply you got in your 'proper error handling' thread -

https://forums.phpfreaks.com/topic/318166-proper-error-handling/?do=findComment&comment=1615758

 

[gizmola]

I suggest you use the advice provided here:  https://phpdelusions.net/pdo#errors

In a nutshell... don't use try catch around PDO code, unless you are checking for a common specific issue you can recover from.  

The problem of a user trying to insert an existing email and thus triggering an exception due to the existence of a unique constraint on that column is one of the common places where you DO want to use a try catch block.  The example provided directly addresses this scenario, and you can see it's really the same thing that Barand gave you.  The answer you should have accepted was Barand's code, even though you had a syntax issue.

Otherwise, most PDO code should not have try ... catch around it.

[Adamhumbug]

Thank you all for the explanation.  I am learning and many of the concepts in discussion are new to me so takes a while for me to properly understand.  I appreciate your patience while i learn.