0

I am in the process of learning PHP and I figured I'd start with prepared statements since they're apparently good practice. I am currently writing a prototype for a registration form which is the first time I use PDO. I managed to get this one to work, to check if the username is not already taken.

$stmt = $conn->prepare('SELECT username FROM `users` WHERE username = :username');
$stmt->bindParam(':username', $username, PDO::PARAM_STR); // Note: bindParam binds to the REFERENCE of the variable passed, only evaluated when execute() is called
$stmt->execute();
$result = $stmt->fetchColumn();

if ($result != NULL){
    die("2:Username already taken !");
}

Works like a charm, but when it comes to INSERTing, i'm having trouble. This non-PDO version works fine (note:yes, I am currently uploading a non-hashed password but that is only until I can get this PDO statement to work, I'll secure all of that then) : 

 $createuserquery = "INSERT INTO users (`iD`, `online`, `username`, `nickname`, `hash`) VALUES (NULL,'0','".$username."','".$username."','".$password."')";
mysqli_query($conn, $createuserquery) or die("4: Insert user query failed"); //error 4 : insert user query failed

I tried converting it, and ended up with this :

$stmt2=$conn->prepare("INSERT INTO users (`iD`, `online`, `username`, `nickname`, `hash`) VALUES (':iD',':online',':username',':nickname',':password')");
$stmt->bindParam(':iD', NULL, PDO::PARAM_INT):
$stmt->bindParam(':online', '0', PDO::PARAM_BOOL);
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':nickname', $username, PDO/:PARAM_STR);
$stmt->bindParam(':password', $password, PDO::PARAM_STR);
$stmt->execute();
echo "0"; //Just my check that the code was read until the end

This returns nothing at all (no 0), meaning I probably have a syntax error somewhere but I can't find what is wrong... after several unsuccessful attempts i tried another approach : a slightly different version, binding only the username and password and preparing the statement as such :

    $stmt2=$conn->prepare("INSERT INTO users (`iD`, `online`, `username`, `nickname`, `hash`) VALUES (NULL,'0',':username',':username',':password')");
    $stmt->bindParam(':username', $username, PDO/:PARAM_STR);
    $stmt->bindParam(':password', $password, PDO::PARAM_STR);

This one DOES return 0, meaning the syntax is probably "ok"... but nothing gets inserted into my database, unfortunately.

I'm officially stuck. Any suggestion ? I'm afraid the answer might be painfully obvious but I can't put my finger on it...

Aeskyphaz
  • 1
  • 3
    Anything look strange here: `PDO/:PARAM_STR` ? – Paul T. Apr 19 '20 at 23:47
  • Woops, that's a copy/paste mistake, sorry about this (I do have PDO::PARAM_STR in my actual code), thanks for pointing it out though ! – Aeskyphaz Apr 19 '20 at 23:50
  • 1
    You don't need quotes around `':iD'`, just use `:iD`. Having quotes there might actually turn that into a string, deactiving the token. – KIKO Software Apr 19 '20 at 23:50
  • Thank you both, just removed the quoted around the placeholders but it still doesn't seem to function – Aeskyphaz Apr 19 '20 at 23:52
  • Set PDO to throw exceptions and make sure you can see them. I've added another link at the top of your question – Phil Apr 19 '20 at 23:54
  • Then it becomes time to do some debugging. Have you switched on error reporting? Or checked the error logs? Then check out [PDO::errorInfo()](https://www.php.net/manual/en/pdo.errorinfo.php) to check for PDO errors. – KIKO Software Apr 19 '20 at 23:54
  • What does something like changing your execute to if(!$stmt->execute()) echo $stmt->error; give you? – TimBrownlaw Apr 19 '20 at 23:54
  • 1
    You cannot bind `NULL` and `0` as parameters. Just omit the `iD` column from your insert statement or if you must, use `bindValue()`. I'll find the relevant post to add to the list of duplicates but really, [My PDO Statement doesn't work](https://stackoverflow.com/questions/32648371/my-pdo-statement-doesnt-work) is all you should need – Phil Apr 19 '20 at 23:57

0 Answers0