CODEWITHSUNDEEP
phpmysql
Wern Ancheta
Wern Ancheta

Reputation: 23297

problems in mysql fields value prefix

I'm using the function below to create a unique field that is prefixed with a letter. Will this cause any problems if its used for transactions with many users? The scenario is that this function is called inside a loop and if there are many users simultaneously using the system. Will this cause any errors?Is there a better way to do this?I'm using the GLAccountID as foreign key to the other tables.

function getID(){
    global $db;
    $max_gl = $db->get_var("SELECT MAX(GLAccountNumber) FROM sys_glaccount");
    if($max_gl == null){
        $max_gl = 1;
    }else if($max_gl > 0){
        $max_gl = $max_gl + 1;
    }
    return 'GLA'.$max_gl;
}

The table looks something like this, GLAccountNumber is the primary key and I set it to auto-increment.

enter image description here

Upvotes: 0

Views: 122

Answers (2)

scott
scott

Reputation: 1070

You can actually get the account id in the format you want without a new row:

SELECT CONCAT('GL',GLAccountNumber) AS GLaccountID FROM `sys_glaccount`

Rather than making a whole new column just to be referenced...

Alternatively, if your intention is to avoid confusion with identical fields, you can use 

SELECT g.GLAccountNumber FROM `sys_glaccount` g INNER JOIN `sys_glaccount2` g2 ON g2.GLAccountNumber=g.GLAccountNumber

Without the query getting confused without unique field names

Upvotes: 1

Nanne
Nanne

Reputation: 64409

There are ways to do this, but you shouldn't really. Seriously. This is heavy and dangerous and the developer after you will cry a bit at night.

Please consider using the accountNumber, and just adding the GLA whenever you retrieve it? This way you have the simple, quick and correctness of the auto-id, and can pretttify it when you want.

Another option is to make it a combined key, with your prefix in one column and the number in the auto-increment, although I don't see why you should want it.

In the end, if all you do is add three letters, you don't need to actually do it in the same field :)

Upvotes: 2

Related Questions