Select Sum in table with PHP/Mysql

Asked

Viewed 3,142 times

1

I have a table with the following fields: date, time and quantity. And I need to make a select sum of it.

To better understand, I have a form where I insert a date, after I insert, I will make a query in the bank and in it I need to return the information of how many 'Qtd' has at a certain time of the chosen date.

Example: I need to add the whole 'Qtd' field on the date 2017-06-07 at 11-00, no longer allow entering values at that time if it exceeds 150 (integer).

This picture illustrates well what I need to do:

I’m using PHP/mysql, but I’m having trouble coming up with this logic. Does Select Sum do this kind of operation? What’s the best way to make that rule?

Can someone help me?

  • 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?

  • It should not, the maximum is 150, to illustrate better: http://developmentopuro.com.br/wp-content/uploads/2017/06/tela.jpg

  • has some reason/business requirement to justify the date being separated from the time or this is a legacy system?

3 answers

3


To make the check:

$select = "SELECT hora,COALESCE(SUM(qtd), 0) FROM table WHERE data='2017-06-07' GROUP BY hora"; //No caso coloquei a hora e data que você utilizou no seu exemplo, mas teria que ser as váriaveis que você pegou do formulário.
$comando = mysqli_query($connect, $select); //$connect é o endereço do servidor.

$count = mysqli_num_rows($comando);
if ($count > 0)
{
    while ($row=mysqli_fetch_row($comando))
    {
        printf ('Hora: %s Quantidade: %s \n', $row[0], $row[1]); //Só para fazer o teste, não necessário.
        if ($row[1] > 150)
        {
            echo "Indisponível.";
        }
        else
        {
            echo "Boa disponibilidade.";
        }
    }
}
else
{
    echo "Boa disponibilidade.";
}

To perform the update:

$select = "SELECT SUM(qtd) FROM table WHERE (data='2017-06-07' AND hora='11-00') GROUP BY hora";
$comando = mysqli_query($connect, $select);
$row = mysqli_fetch_row($comando);
if ($row[0] + $quantidade > 150)
{
    echo "Não existem tantas vagas.";
}
else
{
    $update = "UPDATE table SET qtd=qtd + '$quantidade' WHERE data='2017-06-07' AND hora='11-00'";
    mysqli_query($connect, $update);
}
  • Exactly, the times are already defined, for example, from 11 am to 11 pm, and with intervals of 1 hour. then, from the date, example 2017-06-14, I need to know if you have at 11am any quantity, and what quantity you have registered, 12pm idem and so on -

  • I’ve edited my answer, see if it fits you now.

  • In this case, in this image http://developmentopuro.com.br/wp-content/uploads/2017/06/tela.jpg (which is how it will look), as I would for each of the times? would have some if?

  • I edited again, see if it’s the way you wanted it.

  • Good, it worked here... Now, as would appear with the times that present 0 Qtd?

  • You’d have to use the COALESCE, if the value is null, it returns 0. I edited my answer, see if it fits you well.

  • Oh yes, I understood, It is that in the case, as the loop will be based on time and time, will have date and time that will not be registered in the bank, example, if you have not booked on the date 2017-05-15 to 11h, even so need appear the option for booking, and will have nothing in the bank to select, I could do some if at that?

  • Yes, I edited my answer, see if it is as you wanted now.

  • The only thing that got me confused was that the schedules will be on an available form, so 11:00, 12:00, 1:00... 9:00, 10:00. and in that order, if of count I got a little confused on how to merge the loop with the predefined form

Show 4 more comments

3

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.

  • Very good your logic, but how would I make a Where of specific date using this logic? It would be better to use a while or foreach to present the result?

  • What do you mean? To display or to insert?

  • To display the dates according to the image I showed, according to the specific date

  • 1

    In the select, just do group by only of hora and define the where equal to the desired date, similar to what is in Francisco’s reply.

2

Fiddle: http://sqlfiddle.com/#! 9/c75434/1

Data creation

CREATE TABLE registro (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `data` date,
    `hora` time,
    `quantidade` int
);

INSERT INTO registro   
VALUES (null, "2017-06-06", "13:00:00", 20),
       (null, "2017-06-06", "14:00:00", 30),
       (null, "2017-06-06", "15:00:00", 90),
       (null, "2017-06-06", "15:00:00", 80),
       (null, "2017-06-05", "15:00:00", 100);

Select grouped by date and time:

SELECT data, SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade FROM registro GROUP BY data, hora_minuto

Select grouped by specific date:

SELECT data, SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade FROM registro WHERE data = '2017-06-06' GROUP BY data, hora_minuto

Select by specific date and time

SELECT data, SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade FROM registro WHERE data = '2017-06-06' GROUP BY data, hora_minuto HAVING hora_minuto = '15:00'

Want to know which time has the most quantities (regardless of the day) ?

SELECT SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade FROM registro GROUP BY hora_minuto ORDER BY soma_quantidade DESC

Want to put the message on the return of the select ? (do not recommend)

SELECT data, SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade, IF(SUM(quantidade) > 150, 'Indisponível', 'Boa disponibilidade') AS descricao FROM registro GROUP BY data, hora_minuto

Browser other questions tagged

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