Let’s assume that your table has the following structure:
create table quantidades (
`id` int not null auto_increment,
`data` date,
`hora` time,
`quantidade` int,
primary key (id)
);
Considering some values in the table:
insert into quantidades
values (default, "2017-06-06", "13:00:00", 50),
(default, "2017-06-06", "14:00:00", 60),
(default, "2017-06-06", "15:00:00", 70),
(default, "2017-06-06", "15:00:00", 30);
That is, the system has three registered schedules, each with quantities 50, 60 and 100 (70+30), respectively.
To select the records, displaying the date, time and total amount of each one just do:
select `data`, `hora`, sum(`quantidade`) as `total`
from quantidades
group by `data`, `hora`;
In this way, the three records will be returned:
data | hora | total
-----------+----------+--------
2017-06-06 | 13:00:00 | 50
2017-06-06 | 14:00:00 | 60
2017-06-06 | 15:00:00 | 100
To insert new values, ensuring that the total does not exceed 150, just do:
SET @data = "2017-06-06", @hora = "13:00:00", @quantidade = 60;
insert into quantidades (`data`, `hora`, `quantidade`)
select @data, @hora, @quantidade
where (
select sum(`quantidade`)
from quantidades
where `data` = @data and `hora` = @hora
) + @quantidade <= 150;
The first line:
SET @data = "2017-06-06", @hora = "13:00:00", @quantidade = 60;
Define the variables with the values to be entered into the database. These values will probably be set with PHP. Something like:
SET @data = "{$data}", @hora = "{$hora}", @quantidade = {$qnt};
After, the instruction of insert
but not followed by values
, but of a select
. This so that the values to be entered will only be passed from the select
to the insert
if the condition of select
is true. In this case, the condition is that the sum of the current total plus the quantity to be inserted must be less than or equal to 150. If it is, the values are passed to the insert
, being entered into the database. Otherwise, if the sum between the current total and the quantity to be inserted exceeds 150, the record is not made.
It is important to note that, as the current total is verified before entering a new record, the date and time must already be previously registered in the bank, because if they do not exist, the condition of the select
is not satisfied and registration will never be made.
So for example when inserting a quantity of 90 in 2017-06-06, 13:00:00
SET @data = "2017-06-06", @hora = "13:00:00", @quantidade = 90;
insert into quantidades (`data`, `hora`, `quantidade`)
select @data, @hora, @quantidade
where (
select sum(`quantidade`)
from quantidades
where `data` = @data and `hora` = @hora
) + @quantidade <= 150;
The registration will be done, because at this time the total amount in the bank is 50 and is being inserted 90, totaling 140, which does not exceed 150.
data | hora | total
-----------+----------+--------
2017-06-06 | 13:00:00 | 140
2017-06-06 | 14:00:00 | 60
2017-06-06 | 15:00:00 | 100
However, if you try to insert the same amount at 15:00:00
SET @data = "2017-06-06", @hora = "15:00:00", @quantidade = 90;
insert into quantidades (`data`, `hora`, `quantidade`)
select @data, @hora, @quantidade
where (
select sum(`quantidade`)
from quantidades
where `data` = @data and `hora` = @hora
) + @quantidade <= 150;
Registration will not be done, because at this time there is already a total of 100 and 100+90 exceeds the limit of 150.
data | hora | total
-----------+----------+--------
2017-06-06 | 13:00:00 | 140
2017-06-06 | 14:00:00 | 60
2017-06-06 | 15:00:00 | 100
A record of a quantity of 50, or less, will be made as expected:
SET @data = "2017-06-06", @hora = "15:00:00", @quantidade = 50;
insert into quantidades (`data`, `hora`, `quantidade`)
select @data, @hora, @quantidade
where (
select sum(`quantidade`)
from quantidades
where `data` = @data and `hora` = @hora
) + @quantidade <= 150;
Staying on the table:
data | hora | total
-----------+----------+--------
2017-06-06 | 13:00:00 | 140
2017-06-06 | 14:00:00 | 60
2017-06-06 | 15:00:00 | 150
If you are using PDO, you can check whether the record has been entered or not by checking the number of lines affected by the instruction insert
, through the method PDOStatement::rowCount
. If return 1, the record was entered, but if 0, the record was not entered because the total exceeded 150.
In the case, if there is a total of 140 and an additional 20 is inserted, it should be allowed, because there are only 140 or it should not be allowed, because 140+20 exceeds 150?
– Woss
It should not, the maximum is 150, to illustrate better: http://developmentopuro.com.br/wp-content/uploads/2017/06/tela.jpg
– Rubens Junior
has some reason/business requirement to justify the date being separated from the time or this is a legacy system?
– wryel