mysql - Error SQLSTATE[HY093]: Invalid parameter number: in php PDO statement

im a newbie in php and im facing this problem...i get with it 3 days and im going mad... ;). Im trying to implement a table with users of a web application. So i have to check that the user doesnt exist.

My sql table:

DROP TABLE users;
CREATE TABLE users (
    idUser INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    mail VARCHAR(45) NOT NULL UNIQUE,
    name VARCHAR(45) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    role enum ("admin", "user"), 
    state BOOLEAN,
    forgotpass VARCHAR(32) NOT NULL
);
Copy code

Also this code works ( test if the user already exists in the table):

//$query="SELECT mail FROM proba.users WHERE mail='{$correu}'";
   $sql = 'SELECT  * FROM users WHERE mail = :mailparam';
    //$sql = 'SELECT  * FROM users';
    $stmt = $con->prepare($sql);

  // 2. execute to insert a row 
  // with an associative array
  $stmt->execute(
      array(':mailparam'=>$correuFormulari)
      );

  // 3. get all rows
  $rows = $stmt->fetchAll();
  foreach ($rows as $rowActual) {
    echo $rowActual['mail'] . "<br>";
   echo $rowActual['password'] . "<br>";
 }
Copy code

But following the same logic i cant insert an element:

 if(count($rows) > 0){

        echo "L'usuari ja existeix";
        echo "<p><a href='registreUsuari.php'>Torna</a></p>";

    } else{
        echo "Usuari no trobat. Passem a insertar";
        //INSERT INTO users (mail, password, role, name, created_at,forgotpass) VALUES ("[email protected]", "pepe","user", "pepito", current_Time,"forgotpass");
        $user="admin";
        $sqlinsert = 'INSERT INTO users (mail, password, role, name,forgotpass) VALUES (:mail, :passwordform,:usuari, :nomFormulari,:forgotpass)';
    //$sql = 'SELECT  * FROM users';
        $stmtinsertar = $con->prepare($sqlinsert);
       // $stmt = $con->prepare("INSERT INTO users (mail, password, role, name, created_at,forgotpass) VALUES (:mail, :password,:user, :nomFormulari, :data,:forgotpass)");
      /*  $stmtinsertar->bindParam(':mail', $correuFormulari);
        $stmtinsertar->bindParam(':password', $passwordFormulari);
        $stmtinsertar->bindParam(':user', $user);
        $stmtinsertar->bindParam(':nomFormulari', $nomFormulari);
        //$stmt->bindParam(':data', $data);
        $stmtinsertar->bindParam(':forgotpass', "forgotpass");

        INSERT INTO users (mail, password, role, name,forgotpass) VALUES ("hola@g,ao?�", "pepe","user", "pedro","forgot")
        */
        try
            { 
                //$stmtinsertar->execute();
                $stmt->execute(
                    array(':mail'=> "$correuFormulari",
                    ':passwordform'=>  "$passwordFormulari",
                    ':usuari'=>  "$user",
                    ':nomFormulari'=> "$nomFormulari",
                    ':forgotpass'=> "forgotpass")
                    );
            }
        catch(PDOException $e)
        {
            handle_sql_errors($selectQuery, $e->getMessage());
        }
        

        echo "S'ha creat l'usuari";
        //header('Location: '.'login.php');

//        $stmt->close();
       

    }  
Copy code

I enter in the correct if, but i cant insert into the table.... The error shows something like:

[email protected] : object(PDO)#2 (0) { } hoola0Usuari no trobat. Passem a insertar

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
Copy code

As you can read in the code i also tried to use bind->Params....

Any help would be apreciated.

Also would like to know, which form of executing PDO is preferred, using bindParameter or using an array.

Thanks in advance

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.