Adamhumbug Posted February 18 Share Posted February 18 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? Quote Link to comment https://forums.phpfreaks.com/topic/318276-uncaught-pdoexception-sqlstatehy093-invalid-parameter-number/ Share on other sites More sharing options...
Adamhumbug Posted February 18 Author Share Posted February 18 Quotes around VALUES 🤦♂️ Quote Link to comment https://forums.phpfreaks.com/topic/318276-uncaught-pdoexception-sqlstatehy093-invalid-parameter-number/#findComment-1615939 Share on other sites More sharing options...
Solution Barand Posted February 18 Solution Share Posted February 18 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; } } Quote Link to comment https://forums.phpfreaks.com/topic/318276-uncaught-pdoexception-sqlstatehy093-invalid-parameter-number/#findComment-1615944 Share on other sites More sharing options...
Adamhumbug Posted February 19 Author Share Posted February 19 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 Quote Link to comment https://forums.phpfreaks.com/topic/318276-uncaught-pdoexception-sqlstatehy093-invalid-parameter-number/#findComment-1615989 Share on other sites More sharing options...
Barand Posted February 19 Share Posted February 19 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 1 Quote Link to comment https://forums.phpfreaks.com/topic/318276-uncaught-pdoexception-sqlstatehy093-invalid-parameter-number/#findComment-1615991 Share on other sites More sharing options...
mac_gyver Posted February 19 Share Posted February 19 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 Quote Link to comment https://forums.phpfreaks.com/topic/318276-uncaught-pdoexception-sqlstatehy093-invalid-parameter-number/#findComment-1615994 Share on other sites More sharing options...
gizmola Posted February 21 Share Posted February 21 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. Quote Link to comment https://forums.phpfreaks.com/topic/318276-uncaught-pdoexception-sqlstatehy093-invalid-parameter-number/#findComment-1616148 Share on other sites More sharing options...
Adamhumbug Posted February 21 Author Share Posted February 21 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. Quote Link to comment https://forums.phpfreaks.com/topic/318276-uncaught-pdoexception-sqlstatehy093-invalid-parameter-number/#findComment-1616158 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.