How to tell which query had an error?

Question

I am trying to convert my mysqli database that was very vulnerable to PDO prepared statements. I think i almost got it since it actully inputs the registration data to the database but not to the other databases. So i think there must be some issues on those queries but i can't figure it out. Here below is my code.

<?php
session_start();
// DATABASE CONNECTION
$user = '****';
$pass = '****';

//CREATE CONNECTION
// $conn = new mysqli($dbserver, $dbusername, $dbpassword, $db);
$pdo = new PDO('mysql:host=localhost;dbname=****', $user, $pass);



// ASSIGN VARIABLE FROM FORM
$username = $_POST['username'];
$password = $_POST['password'];
$email    = $_POST['email'];

$password = password_hash($password, PASSWORD_BCRYPT);

// CHECK IF USER IS UNIQUE

    $stmt = $pdo->prepare("SELECT username FROM users WHERE username = :name");
    $stmt->bindParam(':name', $username);
    $stmt->execute();

    if ($stmt->rowCount() > 0) {
        echo "That username already exist!";
    } else {
        //INSERT DATA INTO DATABASE
        $sql = "INSERT INTO users ( username, password, email )
    VALUES ( :username, :password, :email )";
        $sql1 = "INSERT INTO stats (id, username)
VALUES ((SELECT id FROM users WHERE username=':username'), (SELECT username FROM users WHERE username=':username'))";
        $sql2 = "INSERT INTO progression (id, username)
VALUES ((SELECT id FROM users WHERE username=':username'), (SELECT username FROM users WHERE username=':username'))";
        $sql3 = "INSERT INTO powervalues (id, username)
VALUES ((SELECT id FROM users WHERE username=':username'), (SELECT username FROM users WHERE username=':username'))";


        // EXECUTE AND PREPARE
        $query = $pdo->prepare($sql);
        $query1 = $pdo->prepare($sql1);
        $query2 = $pdo->prepare($sql2);
        $query3 = $pdo->prepare($sql3);
        $result = $query->execute(array( ':username'=>$username, ':password'=>$password, ':email'=>$email ));
        $result1 = $query1->execute(array( ':username'=>$username ));
        $result2 = $query2->execute(array( ':username'=>$username ));
        $result3 = $query3->execute(array( ':username'=>$username ));
        //EXECUTE QUERY
        if ($result && $result1 && $result2 && $result3) {
            $_SESSION['Accountsucess'] = "Account has been added sucessfully.";
            header("location: ../../index.php?page=index");
        } else {
            echo "Error database failure";
        }
    }

How error checking works when using PDO, is something you should be able to research/read up on yourself. — CBroe, Dec 06 '17 at 09:38
Just let PDO tell you what's wrong with your query. refer to the question linked — Your Common Sense, Dec 06 '17 at 09:57

score 0 · Answer 1 · answered Dec 06 '17 at 09:42

Instead of continually selecting various parts of information, once you have inserted the user in the users table, fetch the last insert ID and then use that in subsequent calls...

  $sql = "INSERT INTO users ( username, password, email )
    VALUES ( :username, :password, :email )";
  $sql1 = "INSERT INTO stats (id, username)
    VALUES (:id,:username)";


    // EXECUTE AND PREPARE
    $query = $pdo->prepare($sql);
    $query1 = $pdo->prepare($sql1);

    $result = $query->execute(array( ':username'=>$username, ':password'=>$password, ':email'=>$email ));
    // Fetch id of new user
    $id = $pdo->lastInsertId();
    $result1 = $query1->execute(array( ':id' => $id, ':username'=>$username ));

Repeat this same logic for each of the other statements.

How to tell which query had an error?

1 Answers1