AUTO_INCREMENT custom

Question

AUTO_INCREMENT custom

Navigation

#1 by (1 votes)

0

I have in a MYSQL db two tables that are linked:

ADMIN

id - nome - empresa

Each admin adds several values to the docs table:

DOCS

id_doc - id_admim_doc - doc - valor

The id_admim_doc column is a foreign key of the admin table, what I want exactly is that with each new record with msm id_admim_doc a doc is added with AUTO_INCREMENTE type like: doc_1 , doc_2 ...

But each list of each admin must be filled independently, that is, admin 1 has doc_1 , doc_2 ... And in admin 2 as well. Ex: The admin 1 has sla, 20 docs the 2 has only 3, and so on. Does anyone know how to help me?

Ex:

Admin:

id -   nome -    empresa
 1  -  teste  -  teste2
 2  -  teste3 -  teste4


 Docs:

 id_doc    - id_admim_doc -    doc    - valor
   1       -     1        -   doc_1   -    t
   2       -     1        -   doc_2   -    t
   3       -     1        -   doc_3   -    t
   4       -     2        -   doc_1   -    t
   5       -     2        -   doc_2   -    t
   6       -     1        -   doc_4   -    t
   7       -     2        -   doc_3   -    t
   8       -     1        -   doc_5   -    t

php mysql

asked by anonymous 05.06.2018 / 05:52

1 answer

2 calls to the same table in SQLite (output different than expected) Count number of records in a MySql table with PHP?

score 1 · Accepted Answer

You can use trigger to do this using trigger which would leave your insert small

Then when you enter the field doc you enter as null

DROP TRIGGER IF EXISTS  'trg_atualiza_doc';

DELIMITER $$

  CREATE TRIGGER 'trg_atualiza_doc'
    BEFORE INSERT ON 'docs' 
       FOR EACH ROW 
         BEGIN
             SET @total := (SELECT COUNT(*) FROM docs WHERE id_admin_doc = NEW.id_admin_doc);          
             SET @nome := concat( 'DOC_',@total + 1,'');
             SET NEW.doc = @nome;
         END $$

DELIMITER ;