Insert php field concatenating date

Asked

Viewed 70 times

1

I would like to know how to insert the following combination, via PHP or mysql:

I have a field called YEAR that needs to receive the current YEAR 2017 format, via php or mysql.

I have a code field that will need to receive the concatenation of the protocol number + the year, but this protocol would be the table ID field that is generated automatically with AI.

How would you get this ID, even if you hadn’t generated any fields yet, because it is auto_increment.

In short, when making a new registration, he would take the ID + year and put inside the field CODE.

php mysql

  • The code field is auto_increment?

  • No, only the ID is A_I, the code will receive the current ID + year

  • 1

    If you have to do two processes! first enter the record, take the Id and update the Code field with year and id! if there’s any code we can base on?

  • i used an Random to generate this code, but it gets very random, public Function gera_codigo(){ Return substr(time().Rand(10000,99999),-15); }

  • The code field is to sweep the base?

  • That, code field is varchar

  • then if it generated id 1 it would be 12017 if it were id 2 it would be 22017 so on?

  • That’s right, when the year changes it becomes 12018, because they are two primary keys, ID and YEAR, and if the year changes the ID becomes 1 again

Show 3 more comments

2 answers

2


Simple with a function, in the variable code the code you want will be saved and then you can enter the code in the database:

Obs the id is from the database and as it is a code I suppose it should be unique then the id you will also have to be unique.

// $id = $mysql['id'] provem da DB

function generateCode($id){

    //pegar ultimo registo
    $ultimoRegisto = $db->executar("SELECT id FROM denuncia ORDER BY id DESC LIMIT 1"); 

    //verifica a data do ultimo registo se 
    //for igual a data atual, incrementa se não começa denovo com valor 1
    if($ultimoRegisto->num_rows['ano'] == date('Y')){
        return $id . date('Y');
    }

    return 1 . date('Y');
}

  • The year it takes normally 2017, the problem is how I’ll get the ID if it hasn’t even been generated?

  • Try to get the id of the last database record and add one more so you have the unique id to be generated

  • But what if the last ID was 3 of the year 2017 and insert the first registration 2018? it will return the ID 3 and add 1

  • yes ta correct when adding 1 gets 4 , which will be the first entry of 2018, the id has to be unique, just like the code

  • It is that are two primary keys, ID and YEAR, precisely to change the value according to the year and generate the protocol. if the year is always equal it increments the ID, ie I can have several ID 1 in different years.

  • Then you take the last record and compare it to the current year if it’s the same increment if you don’t reset the ID to 1

  • i did so public Function gera_codigo() ! $db = new mysql(); $code = $db->run("SELECT id FROM denounces Where year=year "); Return $code->num_rows.date('Y'); } How do I implement this function you mentioned on Where?

  • edited response.

  • ta almost OK, it did not increment the ID here, ie it is not falling inside the IF

  • Thank you so much for the light you gave, I did the function like this: public Function gera_codigo() ! $db = new mysql(); //pick last record $last record Log = $db->run("SELECT id FROM report Where year=year "); //check the date of the last entry if ///is equal to the current date, increment if it does not start again with value 1 if($ultimoRegister->num_rows > 0){ Return $ultimoRegister->num_rows +1 . date('Y'); } Return 1 . date('Y'); }

  • We are to help you , already working ?

  • Yes, everything ok =D

Show 7 more comments

0

To do directly in Mysql, you can do so. For example, I created a simple table, containing only ID (auto_increment) and CODIGO (to concatenate). INSERT looked like this: 

INSERT INTO tb_teste (codigo) 
    VALUES (
       CONCAT(
           YEAR(NOW()),"-", (
                SELECT AUTO_INCREMENT
                    FROM information_schema.tables
                    WHERE table_name = 'tb_teste'
                    AND table_schema = DATABASE()
                )
        ));

See the result for this example. I made 3:

inserir a descrição da imagem aqui

Just adjust the CONCAT to the pattern you want.

Browser other questions tagged php mysql

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