Creating a loop with php and mysql

Asked

Viewed 206 times

1

I need a help to create a function in PHP.

  • I have a table in the bank at Mysql, where in a column I want to vary the numbering from 1 to 4 per record.

  • For this I have a form where the client fills in and automatically sets an auto increment.

  • That is to say:

User A registered, automatically sets an id 001
User B registered, automatically sets an id 002
User C registered, automatically sets a 003 id
User D registered, automatically sets a 004 id

User And registered, automatically reset id 001

and so on.

Someone can help me create it?

php sql loop auto-increment

  • in case could not be an id the id (primary key can not repeat) what you could do is trigger a second column called id_secundario and work from 1 to 4.

  • Yes it is exactly that, but these id is referring to the system user, I want to assign in this table a record for each user to have access. For this reason I want the loop in "id_secundario", because we received a huge amount of registration and only one user has access to this record, I want to balance the records by users.

1 answer

1


You can work with the Functions Mysql to help you, at the time of Insert just call a programmed function that will already inform you what the next id you can use. I remember that if the id is the primary key your goal will not be able to be met.

NOTE: I recommend that you create the function only at the time of installation.

I leave below an example of a table created to expose the functionality.

-- Entidade criada para testes
CREATE TABLE teste_incremento (
  id int primary key auto_increment,
  id_secundario int,
  nome varchar(200)
);

-- Crio uma função para retornar o próximo id secundário
CREATE FUNCTION RecuperaProximoIdSecundario ()
RETURNS INT RETURN (
  CASE
    WHEN (SELECT COUNT(1) FROM teste_incremento) = 0 THEN 1
    WHEN (SELECT id_secundario FROM teste_incremento ORDER BY id DESC LIMIT 1) = 4 THEN 1
    ELSE (SELECT id_secundario FROM teste_incremento ORDER BY id DESC LIMIT 1) + 1
 END
);

Below I leave the scenario to test, the function definition needs to be done only in creating the database. 

-- Testes para fazer a inserção
INSERT INTO teste_incremento (id_secundario, nome)
VALUES (RecuperaProximoIdSecundario(), "Hiago");

INSERT INTO teste_incremento (id_secundario, nome)
VALUES ( RecuperaProximoIdSecundario(), "Fernando");

INSERT INTO teste_incremento (id_secundario, nome)
VALUES ( RecuperaProximoIdSecundario(), "João");

INSERT INTO teste_incremento (id_secundario, nome)
VALUES ( RecuperaProximoIdSecundario(), "Maria");

INSERT INTO teste_incremento (id_secundario, nome)
VALUES ( RecuperaProximoIdSecundario(), "Reinaldo");

INSERT INTO teste_incremento (id_secundario, nome)
VALUES ( RecuperaProximoIdSecundario(), "Marcos");

INSERT INTO teste_incremento (id_secundario, nome)
VALUES ( RecuperaProximoIdSecundario(), "Joana");

-- Select para ver o resultado para fazer a inserção
SELECT * FROM teste_incremento;

You can see the above example working on Dbfiddler that I made available.

I hope it helped.

  • Oops, it worked right! Vlw brother :D

Browser other questions tagged php sql loop auto-increment

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