Jump to content

Some database questions


phppup
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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 by benanamen
  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by mac_gyver
  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.