About sql and php

Asked

Viewed 83 times

0

How can I limit the amount of data I can enter in a table? And I want to limit the vacancy field for each route.

Table: students. 

CREATE TABLE IF NOT EXISTS `cadastro`.`alunos` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(255) NULL,
  `cpf` VARCHAR(45) NULL,
  `matricula` VARCHAR(45) NULL,
  `comprovanteResidencia` VARCHAR(255) NULL,
  `rotas_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_alunos_rotas1_idx` (`rotas_id` ASC),
  CONSTRAINT `fk_alunos_rotas1`
    FOREIGN KEY (`rotas_id`)
    REFERENCES `cadastro`.`rotas` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Table: routes. 

CREATE TABLE IF NOT EXISTS `cadastro`.`rotas` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NULL,
  `vagas` INT NULL,
  `partida` VARCHAR(45) NULL,
  `chegada` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

With this code using the "TRIGGER" I managed to decrease the vacancies:

delimiter |
  CREATE TRIGGER men_des BEFORE INSERT ON alunos
  FOR EACH ROW  
      BEGIN  
          UPDATE rotas SET vagas=vagas-1 WHERE id=New.rotas_id;
      END;
| 
delimiter ;

I read in the Mysql documentation how the friend here suggested me, but I ended up facing another problem, which is the following, When the vacancies arrive at 0 I can still add more students to it and the count starts to get negative(-1, -2...).

php sql database mysqli

  • Do you want to limit the route quantityVagas field or limit the amount of student records you can include for a given route? For the first case just use a CHECK clause and for the second a Trigger will meet you

  • Limit the amount of student records to each route. Like, in the table routes the field quantityVagas receives 4 vacancies, with this can only register 4 students on that route. Can you give an example of how to use this "Trigger"? , I do not know this command. I thank you for your help.

  • See Mysql documentation: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

  • Thanks for your help.

  • Take a [https://answall.com/tour] tour and see how to use Stackoverflow. Certainly posting "I can’t do it :(" is not the most appropriate way to get an answer.

  • I edited my question, I think it was clearer what I want to do. I’ll be watering. Abrçs.

Show 1 more comment

2 answers

1


You can define your column vagas as unsigned to define that the integer does not allow signal (i.e., it can only be positive):

CREATE TABLE IF NOT EXISTS `cadastro`.`rotas` (
  --- ...
  `vagas` INT unsigned NULL,
  --- ...
);

In addition you can add a restriction with a CHECK:

CREATE TABLE IF NOT EXISTS `cadastro`.`rotas` (
  --- ...
  `vagas` INT unsigned NULL CHECK (vagas > 0),
  --- ...
);

0

My problem was: When I enrolled a new student in a vacancy, in the vacancy I could not limit the number of students to the same, when I passed the number of vacancies available, in case less than 0, the count was negative(-1,-2, ...). When solving this first problem, came the second, which was: When updating the student’s route, the vacancies did not update the number of vacancies, which in the case was to decrease the same, but I was able to solve these two problems with this code using TRIGGRER: 

    delimiter |
    CREATE TRIGGER dec_vagas BEFORE INSERT ON alunos
    FOR EACH ROW  
        BEGIN
        UPDATE rotas SET vagas=vagas-1 WHERE id=NEW.rotas_id;
        IF (SELECT r.vagas FROM rotas r JOIN alunos a ON r.id=a.rotas_id ORDER BY rotas_id DESC limit 1) < 0 THEN
            SET NEW='Error: Vagas esgotadas!';
            UPDATE rotas SET vagas=0 WHERE id=NEW.rotas_id;
        END IF;
    END;
   | 
   delimiter ;

But thank you all for your help.

Browser other questions tagged php sql database mysqli

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