Some database questions

[phppup]

NULL, NOT NULL, or none?

I've read that if a field is NOT NULL, then a new row cannot start until the field is filled.

Does this mean that if several users are completing a two part form which will be inserted and then updated, that users completing the form will be "frozen out" until the lead user completed part 2?

What exactly occurs and how is it best approached?

 

If PHP is verifying that a username is unique before allowing submission, is there any advantage to using A UNIQUE field in the SQL table?

 

Is it necessary/advised to put a number quantity for field characters [ie: name varchar(22)]  ?  Are characters beyond the indicated size simply dropped off? Why not simply set everything to the maximum? What is the default? Is this necessary for an auto-increment field?

[benanamen]

49 minutes ago, phppup said:

If PHP is verifying that a username is unique before allowing submission

Php should not be verifying that a username is unique. You create a race condition by doing that. Set a Unique constraint on the DB, Attempt the insert, catch the duplicate error if any. This is one of the few instances to use a try/catch block.

Edited July 21, 2022 by benanamen

[Strider64]

Here's a procedural function that I did a long time ago demonstrating this:

 

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) {

        /*
         * 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;
}

I do have to give credit to someone on a different forum help in helping me with the code, but I did rewrite the code to spruce it up and make it a little more streamline.

HTH - John

[phppup]

@Strider64 so how would I adapt this instead of a query to SQL that is trying to determine if a username already exists?

I'm trying to research and understand race conditions. I always thought that code just moved in a straight line.

[benanamen]

11 minutes ago, phppup said:

I'm trying to research and understand race conditions.

In this instance, here is the race condition...

When two or more users make a simultaneous availability check for the same username, the code will "lie" to all the requests and say it is available but only the first request to complete the insert will be valid, the rest of the requests will fail provided you have set a required unique constraint on the DB column. In a low traffic site, you are not likely to encounter simultaneous requests for the same exact username, nevertheless, no point in building in the the race condition when a simple solution exists.

Edited July 21, 2022 by benanamen

[mac_gyver]

3 hours ago, phppup said:

I always thought that code just moved in a straight line.

real-time, multitasking, interrupt/event driven operating systems are a thing now. any one instance of your code does run from start to finish, but it can be interrupted several times for other things that are going on on the computer. and in fact, php will pause, allowing the processor to service other tasks, while waiting for a database query to execute.

[mac_gyver]

5 hours ago, phppup said:

I've read that if a field is NOT NULL, then a new row cannot start until the field is filled.

i don't what you are reading, but that's incorrect. if db column is defined as NOT NULL, it simply cannot hold a null value. if you insert/update the column to a null value, it produces an error and the insert/update query will fail. the column must have a non-null value in it.

5 hours ago, phppup said:

Does this mean that if several users are completing a two part form which will be inserted and then updated, that users completing the form will be "frozen out" until the lead user completed part 2?

no. every instance of your php script and any data that they insert/update are handled by completely separate processing threads. for the usage you have described, the last insert id from any insert query will be valid for that instance of the script.

 

5 hours ago, phppup said:

is there any advantage to using A UNIQUE field in the SQL table?

your database MUST enforce uniqueness, due to the race condition already mentioned. since you must do this, there's no good reason to run a SELECT query first, since the actual insert/update query can fail with a duplicate error if another instance of your script inserted/updated the data first. so, Keep It Simple (KISS), just attempt to insert/update the data and detect if a duplicate index error occurred.

Edited July 22, 2022 by mac_gyver