db INSERT with try & catch

[phppup]

Finally found success after my last debacle.

Now it's time to INSERT my data into a table.

My data is $firstname, $lastname, $username

Since the $username is UNIQUE, it seems advisable to use a try & catch statement.

How do I isolate the $username to do this effectively?
How is this method better than using IF statements?

[mac_gyver]

2 minutes ago, phppup said:

How is this method better than using IF statements?

you ALWAYS need error handling for statements that can fail. the simplest way of adding error handling for all the database statements that can fail - connection, query, prepare, and execute, WITHOUT adding logic at each statement, is to use exceptions for errors and in most cases simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information  via an uncaught exception error (database statement errors will 'automatically' get displayed/logged the same as php errors.) this saves you from having to write out a ton of conditional statements where they won't do your application any good.

the only database statement errors that are recoverable by the user on a site are when inserting/updating duplicate or out of range user submitted values. this is the only case where your application should handle database statement errors, and since you are using exceptions for database statement errors, this is the only case where your application code should have a try/catch block.

your catch code would test if the error number is for something that your code is designed to handle (a duplicate index error number is 1062),  and setup a message for the user telling them exactly what was wrong with the data they they submitted. for all other error numbers, just rethrow the exception and let php handle it. if there's only one column defined as a unique index, a duplicate index error would mean the duplicate was in the column. if you have more than one unique index, you would execute a SELECT query within the catch code to find which column(s) contain duplicate values.

[phppup]

Does one bad apple (the unique value of already exists) spoil the entire insert?

$firstname and $lastname (although valid) are rejected and need to be re-submitted of the $username already exists?

[Psycho]

25 minutes ago, phppup said:

Does one bad apple (the unique value of already exists) spoil the entire insert?

$firstname and $lastname (although valid) are rejected and need to be re-submitted of the $username already exists?

If I understand your question correctly - yes. If there is an error because of a duplicate key constraint the entire record does not get added, even if other values were "OK". Here is some example code of how to use a try/catch for an INSERT with a duplicate check

$pdo = new PDO("mysql:host=$hostname;dbname=$databasename;charset=utf8", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Enables exception mode

try {
    $stmt = $pdo->prepare("INSERT INTO REGISTRY (uname, fname, lname) VALUES (:uname, :fname, :lname)");
    $stmt->execute([$username, $firstname, $lastname]);
} catch (PDOException $e) {
    if ($e->getCode() == 1062) {
        // Duplicate user
    } else {
        throw $e;// in case it's any other error
    }
}

 

[mac_gyver]

44 minutes ago, phppup said:

are rejected and need to be re-submitted of the $username already exists?

yes. but since your form processing code and form are on the same page and you are safely repopulating the form field values with the submitted form data, this should not be a problem. just correct the username value and resubmit the form.

[phppup]

Thanks for the example @Psycho 

What does this look like in procedural format?  I've seen examples in procedural that use 

$ex->getMessage()

Is that interchangable with getCode?

[mac_gyver]

the point of the catch code is to test the error number and just handle specific error number(s).  getMessage() is the error text and won't do you any good for testing the error number. these are both OOP methods, not procedural code.

BTW - getCode() is the sqlstate, which groups together several similar errors, and is not the actual error number.

[benanamen]

1 hour ago, mac_gyver said:

BTW - getCode() is the sqlstate, which groups together several similar errors, and is not the actual error number.

I was just going to point that out. The if should be

if ($e->getCode() == 23000) { // Duplicate user }

1062 is held in the errorInfo array

Edited September 13, 2022 by benanamen

[phppup]

Thanks for the input from everyone.

I'm trying to digest all the info, but I'm still a bit uncertain as to why this is better than my code that checks (and prevents) the acceptance of a name that already exists.

Isn't something like

$sql="SELECT username from table where username = $username";
if($result = mysqli_query($link, $sql)){
    if(mysqli_num_rows($result) > 0){
        //BINGO! it already exists               //take appropriate action
     } else {
//proceed with the insert 
  }
        

doing the same thing?

[benanamen]

3 minutes ago, phppup said:

Isn't something like doing the same thing?

No. You are building in a race condition by checking availability.

[mac_gyver]

no. not only does it take two queries and more code, but when you try to execute a SELECT query to find if data doesn’t exist, a race condition exists where multiple concurrent instances of your script will all find that the value doesn’t exist and attempt to insert it. the database is the last step in the process. it must enforce uniqueness. since the database must do this, you might was well eliminate the initial SELECT query, which can tell your code the wrong answer.

[phppup]

16 minutes ago, mac_gyver said:

the initial SELECT query, which can tell your code the wrong answer.

So, if I'm comprehending this correctly, it not do much about determining whether the unique element is available (which the SELECT will accomplish) but MORE about determining whether the value is INSERTable (which is defined in absolute terms by the field being unique).

[Psycho]

  

1 hour ago, phppup said:

Thanks for the input from everyone.

I'm trying to digest all the info, but I'm still a bit uncertain as to why this is better than my code that checks (and prevents) the acceptance of a name that already exists.

I never saw any code that you posted. And, if you like your code, why are you asking?

41 minutes ago, phppup said:

So, if I'm comprehending this correctly, it not do much about determining whether the unique element is available (which the SELECT will accomplish) but MORE about determining whether the value is INSERTable (which is defined in absolute terms by the field being unique).

Checking to see if there is a duplicate BEFORE inserting the record creates the possibility for a race condition. Depending on the needs of your application this may not be a concern, but it is not the "right" way to do it. Here's an example of what can happen in a race condition.

Two users submit the form for the same user ID that does not yet exist in the database. User #1's request hits the server first. The code checks to see if the username exists and see that it does not. Now, before the code can proceed to the insert, the request for User #2 reaches the server and does a Select statement to see if the value exists. Since User #1's request hasn't yet completed the insert statement, no duplicate will be found. User #1's request will be inserted, but since the validation for the duplicate check passed for User #2 is request will be inserted as well. But, of course, it will fail if there is a duplicate constrain - but then you'd have to add the same logic as you would need to check during the initial insert.

Granted, a race condition requires two users to submit such records at almost the same instant. So, based on your application this may not even be a consideration. But, it is the right way to do it

[phppup]

21 minutes ago, Psycho said:

And, if you like your code, why are you asking?

 

21 minutes ago, Psycho said:

But, it is the right way to do it

Thanks for the explanation.

And that's why I asked. Just because I "like" my code doesn't mean it's doing everything that it should be doing as effectively as it could be doing it. And I want to learn and stride to do it the right way.

 

Aside from instances with a unique parameter,should I be doing this (try & catch) regularly with all my INSERT statements?

It would probably reduce the amount of code and PHP checks (ie: input length) while putting a heavier burden on the SQL database. Is that advisable? Preferable?

Learned allot. Thanks again everyone.

[mac_gyver]

7 minutes ago, phppup said:

Aside from instances with a unique parameter,should I be doing this (try & catch) regularly with all my INSERT statements?

no. someone already went to great detail specifically stating when to use error handling in your application code and when not to.

[phppup]

Got it.

One last thing. Is this

5 hours ago, Psycho said:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Enables exception mode

only needed in PDO?

Is there a procedural equivalent? Or is it defaulted?

[Strider64]

/*
 * As long as you have the correct field names as the key and
 * the correct values in the corresponding keys the following
 * procedural function should work with no problem.
 *
 */


function insertData(array $data, $pdo, $table) {
    try {
        /* Initialize an array */
        $attribute_pairs = [];

        /*
         * Set up the query using prepared states with the values of the array matching
         * the corresponding keys in the array
         * and the array keys being the prepared named placeholders.
         */
        $sql = 'INSERT INTO ' . $table . ' (' . implode(", ", array_keys($data)) . ')';
        $sql .= ' VALUES ( :' . implode(', :', array_keys($data)) . ')';

        /*
         * Prepare the Database Table:
         */
        $stmt = $pdo->prepare($sql);

        /*
         * Grab the corresponding values in order to
         * insert them into the table when the script
         * is executed.
         */
        foreach ($data as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = $value; // Assign it to an array:
        }

        return $stmt->execute($attribute_pairs); // Execute and send boolean true:

    } catch (PDOException $e) {

        /*
         *
         * getCode() is the sqlstate
         *
         * echo "unique index" . $e->errorInfo[1] . "<br>";
         *
         * An error has occurred if the error number is for something that
         * this code is designed to handle, i.e. a duplicate index, handle it
         * by telling the user what was wrong with the data they submitted
         * failure due to a specific error number that can be recovered
         * from by the visitor submitting a different value
         *
         * return false;
         *
         * else the error is for something else, either due to a
         * programming mistake or not validating input data properly,
         * that the visitor cannot do anything about or needs to know about
         *
         * throw $e;
         *
         * re-throw the exception and let the next higher exception
         * handler, php in this case, catch and handle it
         */

        if ($e->errorInfo[1] === 1062) {
            return false;
        }

        throw $e;
    } catch (Exception $e) {
        echo 'Caught exception: ', $e->getMessage(), "\n"; // Not for a production server:
    }

    return true;
}

Here's a detailed explanation of catching a duplicate username. Someone explained to me a long time ago that this was the proper way to catch duplicates especially if your website has a lot of traffic on it. You could do the following to check if a username already exists, BUT it doesn't prevent duplicates as two people could be entering the exact username at the same time.  Sorry it's part of a Class, but this is just use as a courtesy to whoever is registering as the odds entering at the same username at the same time is not that great. 

 

    public static function usernameCheck($username): array
    {


        if (isset(static::$table)) {
            $query = "SELECT username FROM " . static::$table ." WHERE username = :username";
        }
        $stmt = Database::pdo()->prepare($query);
        $stmt->bindParam(':username', $username);
        $stmt->execute();
        if ($stmt->fetch(PDO::FETCH_ASSOC)) {
            $data['check'] = true;
            return $data;
        }

        $data['check'] = false;
        return $data;


    }

 

Edited September 14, 2022 by Strider64

[Psycho]

@Strider64 Maybe I am misreading your code, but I think I see a bug. In the block of code to consolidate the values into the array $attribute_pairs to perform the insert there is a condition that checks if the index is "id" - if so, it skips adding that value to the $attribute_pairs. But, there is no similar logic in the code block that dynamically creates the list of fields for the sql query. That code uses all the indexes in the $data array using array_keys($data). So, if the array $data did contain an index with the name "id" the query would fail because the number of fields for the insert would be one greater than the number of values. Either the "id" field needs to be excluded in the fields list OR the value if such a field exists can be set to NULL. Personally, I would leave such logic out since any code passing data to an INSERT function should only pass the relevant fields. Although, I could see the viability of including the ID if the function was doing an ON DUPLICATE KEY UPDATE.

Or, I could be completely missing something and would appreciate pointing what that is.

[benanamen]

@Strider64, you have been posting that insertData function in various forums for some time now. It doesn't work the way you think it does. HINT: Try passing in an array that has "id". 🙂

[Strider64]

4 hours ago, benanamen said:

@Strider64, you have been posting that insertData function in various forums for some time now. It doesn't work the way you think it does. HINT: Try passing in an array that has "id". 🙂

You're right that shouldn't even be in there in the first place. That was part of the update function.  I just copy that part over to the insert function when I wrote that code as I never really tested it out as insert should never have an id it in the first place. Me bad. 🙄

Edited November 6, 2022 by Strider64