Jump to content

Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number


Go to solution Solved by Barand,

Recommended Posts

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?

  • Solution

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;
    }
}

 

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

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

  • Thanks 1
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

 

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.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.