Generate sequential numbers for draw

-2

I'm creating an online raffle system in PHP and MySQL. Firstly I create the prize draw where I inform the prize and its details, among them the amount of coupons, starting in a specific number. For simplicity, I'll raffle a car and sell 800 numbers, which should start at 101 and go up to 900 (101, 102, 103, ... 899, 900). In another table, I need to generate these numbers automatically, and in each of them I will have the name of the buyer of this number, phone and email, which will be filled in later. How do I generate this sequence and write to the database? The table looks like this:

CREATE TABLE 'numeros' (
  'id' int(11) NOT NULL,
  'rifa' varchar(50) NOT NULL,
  'numero' decimal(8,0) NOT NULL,
  'status' varchar(50) NOT NULL,
  'nome' varchar(250) NOT NULL,
  'celular' varchar(50) NOT NULL,
  'email' varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Only the number field must be generated at this stage, in addition to id, of course. I tried different ways, I could not. I gratify your help.

    
asked by anonymous 18.10.2018 / 20:39

1 answer

0
  

Procedures are routines defined in the database, identified by a name by which they can be invoked. Such a procedure can execute a series of statements, receive parameters, and return values.

CREATE TABLE 'numeros' (
  'id' int(11) NOT NULL auto_increment primary key,
  'rifa' varchar(50) NOT NULL,
  'numero' decimal(8,0) NOT NULL,
  'status' varchar(50) NOT NULL,
  'nome' varchar(250) NOT NULL,
  'celular' varchar(50) NOT NULL,
  'email' varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

delimiter //
create procedure populate (in num int)
begin
declare i int default 101;
while i < num do
insert into numeros (numero) values (i);
set i = i + 1;
end while;
end //
delimiter ;

call populate (901);
  • DELIMITER - Used to mark where the procedure starts and ends, in this case I chose //
  • CREATE PROCEDURE - will create the procedure, note that there are parameters for this procedure. The parameters can be IN, input only, OUT, output only and INOUT, input and output. The data types of the parameters are the same data types as the DBMS (INT, VARCHAR (), TEXT, BLOB ...)
  • In our case we have the variable num , which will be passed to procedure
  • Between the commands BEGIN and END will be placed the commands executed by the procedure.
  • While ... Statement While-executes the commands within the loop, while the Boolean expression is true '.

The formal syntax of the While ... End while control structure is:

While(Expressão_Booleana)
   instrução(ções)
End while

In our case while i (starting at 101) is less than the num variable passed to procedure (901) makes the following statement

insert into numeros (numero) values (i);

    
19.10.2018 / 04:41