Can't Insert Data into Database using PDO

Question

After sanitizing and validation, which works fine. I tried inserting data into my database but it keeps saying error: "Sorry, we were not able to sign you up... Refill the form properly"

$qry = "INSERT INTO users (email, firstName, surname, userName, password, userDOB) values (?, ?, ?, ?, ?, ?)";

$q = $conn->prepare($qry) or die("ERROR: " . implode(":", $conn->errorInfo()));

$q->bindParam(1, $email);
$q->bindParam(2, $name);
$q->bindParam(3, $surname);
$q->bindParam(4, $username);
$q->bindParam(5, $password);
$q->bindParam(6, $userDOB);

$q->execute();
if(!$q->execute()) {
echo "<h1> Sorry, we were not able to sign you up... Refill the form properly </h1>";
}
else {
echo "<h1> Congratulations, $name ! You have been successfully signed up! </h1>";
}

Any help that will make this work would be greatly appreciated.

any errors? You should catch the PDO Exception as well to get a more detailed error report — A.O., Oct 24 '13 at 18:10
http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo — Your Common Sense, Oct 24 '13 at 18:10
do something like `... } else { die($conn::errorInfo); }` so the DB can TELL you what the error is. fixed messages are fine to display to end users, but when debugging, NEVER use them unless they also include the actual details of the problem. — Marc B, Oct 24 '13 at 18:10
Don't do that thing above. Just configure PHP error reporting properly — Your Common Sense, Oct 24 '13 at 18:12

score 0 · Answer 1 · answered Oct 24 '13 at 18:16

Not sure if it's the issue, but you are calling execute() twice.
Anyway, your only problem is lack of error reporting. Enable it and run every operator only once:

error_reporting(E_ALL);
ini_set('display_errors', 1);
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql = "INSERT INTO users (email, firstName, surname, userName, password, userDOB) 
                   values (?, ?, ?, ?, ?, ?)";
$stm = $conn->prepare($sql));
$stm->execute([$email,$name,$surname,$username,$password,$userDOB]);
if ($stm->rowCount())
{
    echo "<h1> Sorry, we were not able to sign you up... Refill the form properly </h1>";
} else {
    echo "<h1> Congratulations, $name ! You have been successfully signed up! </h1>";
}

score 0 · Answer 2 · edited Apr 25 '16 at 11:03

Thank you guys a whole lot. It works, but is this best practice to write this code and to also avoid SQL Injection?

try {
        $conn = new PDO('mysql:host=localhost; dbname=userdetails', 'root', ''); 
        $conn->setAttribute(PDO:: ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo 'Connected!';
    }
catch(PDOException $pe) {
        echo('Connection error, because: ' .$pe->getMessage());
    }

//Insert data to Database if values are not empty and sanitized
if (!empty($_POST["firstName"]) && !empty($_POST["surname"]) && !empty($_POST["email"]) 
&& !empty($_POST["userName"]) && !empty($_POST["password"]) && $dob_day > 0 && $dob_month > 0 && $dob_year > 0)
{
    $qry = "INSERT INTO users (email, firstName, surname, userName, password, birthday) values (?, ?, ?, ?, ?, ?)";

    $q = $conn->prepare($qry) or die("ERROR: " . implode(":", $conn->errorInfo()));

    $q->bindParam(1, $email);
    $q->bindParam(2, $name);
    $q->bindParam(3, $surname);
    $q->bindParam(4, $username);
    $q->bindParam(5, $password);
    $q->bindParam(6, $userDOB);

    try {
    $q->execute();
                echo "<h1> Congratulations, $name ! You have been successfully signed up! </h1>";
    }
    catch(PDOException $pe) {
        echo('Connection error, because: ' .$pe->getMessage());
    }
}

Yes, protection is all right but error reporting and overal efficiency could be better. — Your Common Sense, Oct 25 '13 at 11:43

Can't Insert Data into Database using PDO

2 Answers2

Linked