AUTO_INCREMENT customized

Asked

Viewed 72 times

0

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

  • ADMIN

    id - nome - empresa

Each admin adds several values in the Docs table:

  • DOCS

    id_doc - id_admim_doc - doc - valor

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

But every list of every admin be completed independently, that is to say in admin 1 ha doc_1, doc_2... And in the admin 2 also. Ex: O admin 1 has sla, 20 Docs the 2 has only 3, and so on. Someone knows 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

  • Could you improve your question? It was not very clear.

  • @War, man, look at the final example, it’s kind of a auto_increment for each id_admin_doc different, you know? See there, all q has value 1, has Docs 1,2,3,4 and 5, see that with value 2 has 1,2 and 3, if I add another doc pro admin with id 2, his new doc would be 4, and if it were from admin with id 1, the doc would be 6, understand?

  • And if there was a new admin with id 3, then start a new one for him, doc_1, 2 ,3...

  • Use Rigger to do this

1 answer

1


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

Then when you enter the field doc you insert 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 ;

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.