CODEWITHSUNDEEP
phpmysql
Muhammad Sipra
Muhammad Sipra

Reputation: 846

auto increment in Mysql database table with my own choice

I have following table named oder in mysql database 

         id    user_id   item_id 
         1       55      5813    
         2       55      359     
         3       56      559  
         4       62      4536    
         5       62      484     
         6       99      4698    
         7       29      435

In this table "id" is primary key and auto increment on this column is on. Now I want to add a column, it should also be auto incremented and value are like OD-001 , OD-002. Then my table should look like 

         id    user_id   item_id  order_custom_id
         1       55      5813     OD-001
         2       55      359      OD-002
         3       56      559      OD-003
         4       62      4536     OD-004
         5       62      484      0D-005
         6       99      4698     OD-006
         7       29      435      OD-007

Note: I am currently working on the PHP Laravel framework. I have go through many questions and articles but nothing worked out for me,

Upvotes: 1

Views: 444

Answers (3)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

If I understand correctly, you want to generate column values based on the auto-increment column values (e.g. 1 -> OD-001).

The first idea that popped into my mind was to use auto computed columns. Unfortunately, MySQL does not seem to support this feature. (it is related to how the auto-incremented and auto computed column values are generated internally):

Generated column 'computed_col' cannot refer to auto-increment column.

Another way is to use a trigger to update another column on insert, but personally I avoid triggers if possible, so using a view can be a decent solution:

CREATE VIEW orderView AS
SELECT id, user_id, item_id, CONCAT('OD-', LPAD(id, 3, '0')))
FROM order

The only disadvantage I see is that values are not persisted and are computed on the fly (more CPU). However, this should work just fine for a fairly small amount of selected rows (< thousands).

NOTE: using a view instead of a separate column also obeys 3NF

Upvotes: 1

Peter
Peter

Reputation: 9113

You could try something like:

INSERT INTO table (user_id, item_id)
VALUES (20, CONCAT('OD-', LPAD((SELECT ISNULL(MAX(id) + 1, 1) FROM table), 3, '0')));

This will select the next incremental id from your table, and prepend that to OD- with leading zeros.

Upvotes: 0

Nerea
Nerea

Reputation: 2147

If want to do this by php and you have this table like a Model and your order_custom_id always end as your id you can use laravel events and create it by getting the last id of your table + 1. 

public static function boot()
{
    parent::boot(); // Validate the model
    static::creating(function ($your_model) {
        $prev_model = YourModel::orderBy('id', 'desc')->first();
        $id = ($prev_model) ? ($prev_model->id +1) : 1;
        $your_model->order_custom_id = sprintf('OD-%s', str_pad($id, 3, "0", STR_PAD_LEFT));
    });
}

Upvotes: 1

Related Questions