MySQL querying failing?

Question

My MySQL query keeps failing and I can't understand why, I read up on it and apparently I was missing the backticks (`) around the table names etc, so I added them and no change. Here is the code:

$db = new PDO("mysql:host=".$db_host.";db_name=".db_name, $db_user, $db_pass);

    try{
        $check = $db->prepare("SELECT `userID` from `userData` WHERE `userID` = :accountID");
        $check->bindParam(':accountID', $accid, PDO::PARAM_INT);
        $check->execute();
        if(!$check->execute()){
            die(var_dump($db->errorInfo()));
        }else{
            if($check->rowCount() > 0) {
                $creditsQuery = $db->prepare("SELECT `userCredits` FROM `userData` WHERE `userID` = :accountID3");
                $creditsQuery->bindParam(":accountID3", $accid, PDO::PARAM_INT);
                $creditsQuery->execute();
                //Set Credits To Variable From Database Column
                $credits = $creditsQuery->fetch(PDO::FETCH_ASSOC);
            }else{
                $sql = $db->prepare("INSERT INTO `userData` (`userID`, `userCredits`) VALUES (:accountID2, '0')");
                $sql->bindParam(':accountID2', $accid, PDO::PARAM_INT);
                $sql->execute();
                if(!$sql){
                    die('Server Error: 404Insert, Please Contact A Member Of Staff If This Error Continues.');
                }
            }
        }
    }catch(PDOException $e){
        die ("Server Error: 404Connection, Please Contact A Member Of Staff If This Error Continues.");
    }

The errorInfo line displays: array(3) { [0]=> string(5) "00000" [1]=> NULL [2]=> NULL }

So, the database successfully connects as the try block doesn't throw an exception. So I really don't know.

thanks,

Matt

Answer 1

You have several errors in your code

• PDO is not in exception mode
• $accid contains no value
• you are using bindParam instead of bindValue - there are differences between them, quite significant ones
• you don't have to wrap anything with backticks unless it's a MySQL reserved word
• you are checking for existence of record in PHP - that's MySQL's job. PHP can't accurately determine if a record exists in MySQL. There's a tiny delay between PHP and MySQL connection (tiny in human measurement, eons in computer measurement). By the time the result arrives and gets parsed by PHP, another process could have inserted the record. That's why we let MySQL take care of uniqueness and data integrity.

Please, read the comments of the code I'm posting and adjust according to your needs:

<?php

/**
 * Create PDO object and set it into exception mode
 */

try {
    $db = new PDO("mysql:host=".$db_host.";db_name=".db_name, $db_user, $db_pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    exit;
}


try {
    // Replace with actual value
    $user_id = 1; 

    // Pay close attention to IGNORE keyword - it's extremely important
    // and it requires a unique index to be placed on userID column
    // I assumed that userID is primary key
    $query = "INSERT IGNORE INTO userData (userID, userCredits) VALUES (:userID, 0)";

    $stmt = $db->prepare($query);

    $stmt->bindValue(':userID', $user_id);

    $stmt->execute();

} catch(PDOException $e) {
    echo 'An error occurred: '. $e->getMessage();
}