CODEWITHSUNDEEP
phpmysql
wobsoriano
wobsoriano

Reputation: 13432

PHP Incrementing Max ID

Hello so I have a code that will check if a username exists and if yes, I want to set the variable $var to the present accountID of the existing username. If the username doesn't exist, I want the MAX of the latest accountID to increment. Here is my code:

$checkUname = $conn->prepare("SELECT accountID from accounts WHERE username=?");
$checkUname->execute(array($_POST['txt_un']));
$row = $checkUname->fetch(PDO::FETCH_ASSOC);
if($row > 0) {
    $var = $row['accountID'];
} else {
    $getMax = $conn->query("SELECT MAX(accountID) as maxAccountID FROM accounts");
        $row = $getMax->fetch(PDO::FETCH_ASSOC);
            $maxID = $row['maxAccountID'];
            $maxID++;

        $var = $maxID;
}

$UpdateTable = $conn->prepare("UPDATE otherTable SET someField=? WHERE otherTableID=?");
$UpdateTable->execute(array($var, $_POST['tableID']));

Now my problem here is, what if 2 or more users will click and run the code above at the same time? Will there be issues on the incrementing ID?

Upvotes: 2

Views: 1391

Answers (4)

Mohammed Elhag
Mohammed Elhag

Reputation: 4302

In General No Problem . why?

It depends on so many factors starting from the ability of DB for performing such queries , the serer processing ,internet connection etc ...

go through links below to get real and specific info

How many MySql queries/second can be handled by a server?

http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

Important :

What is PDO?

PHP Data Objects, or PDO, is a database abstraction layer specifically for PHP applications. PDO provides a consistent API for your PHP application regardless of the type of database server your application will connect to. In theory, if you are using the PDO API, you could switch the database server you used, from say Firebird to MySQL, and only need to make minor changes to your PHP code.

Other examples of database abstraction layers include JDBC for Java applications and DBI for Perl.

While PDO has its advantages, such as a clean, simple, portable API, its main disadvantage is that it doesn't allow you to use all of the advanced features that are available in the latest versions of MySQL server. For example, PDO does not allow you to use MySQL's support for Multiple Statements.

http://php.net/manual/en/mysqli.overview.php

Upvotes: 1

SergeyAn
SergeyAn

Reputation: 764

Here is some extract from this link:click me

It is not exactly what you need, but it has an approach to deal with such problems. And it impies that you are using MySQL InnoDB. If you use MyISAM or MariadDB the solution might be different.

Extract:

Let us look at another example: We have an integer counter field in a table child_codes that we use to assign a unique identifier to each child added to table child. It is not a good idea to use either consistent read or a shared mode read to read the present value of the counter because two users of the database may then see the same value for the counter, and a duplicate-key error occurs if two users attempt to add children with the same identifier to the table.

In this case, there are two good ways to implement reading and incrementing the counter:

First update the counter by incrementing it by 1, and then read it.

First perform a locking read of the counter using FOR UPDATE, and then increment the counter.

The latter approach can be implemented as follows:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

The preceding description is merely an example of how SELECT ... FOR UPDATE works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table.

Upvotes: 1

fbiazi
fbiazi

Reputation: 387

That's why you should have accountID to be an autoincrement column https://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Then you can use LAST_INSERT_ID() to get the id of the inserted user. The page above have a link to this function.

PHP have specific functions to use LAST_INSERT_ID(), each library have its specific function:

Upvotes: 1

Harshit
Harshit

Reputation: 5157

You could use this query to get data at once

IF EXISTS (SELECT accountID from accounts WHERE username=?) THEN
    SELECT accountID from accounts WHERE username=?;
  ELSE 
    SELECT MAX(accountID) as maxAccountID FROM accounts;
  END IF;

Upvotes: 1

Related Questions