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
);
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>";
}
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();
}
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
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