Count Amount of Uploads in a Certain Period

Question

I have the following query in my file:

SELECT IDUsuario,IDArquivo,Time FROM uploads
    WHERE Time >= '2018-02-20 00:00:00'
    AND Time < '2018-03-21 00:00:00'

And my result resembles that:

In this case I have repeated user ID because they have uploaded more than one file and gained point for it , my doubts are as follows:

1 - It would be possible for me to count in a user IDS query ?
2 - If there is , what would be the best method to use php to count users or perform this by mysql?

It will not be routine , only on bonus dates will we use the system.

Answer 1

Could I perform the count in a query only of users IDS ?

Yes, it is possible to count how many uploads the user has made. Just use COUNT and group by id. Example:

SELECT `id`, COUNT(`id`) AS total FROM `uploads` GROUP BY `id`

Here it will group all users by ID and then you’ll count how many records that ID grouped has.

And to filter by date, just use the operator BETWEEN. With this operator you can pass the initial date and the final date. Example:

"SELECT `id`, COUNT(`id`) AS total FROM `uploads` WHERE `date_added` BETWEEN '2018-02-20 00:00:00' AND '2018-02-22 00:00:00' GROUP BY `ID`"

Here it will also count the number of uploads per user, but now it will filter between the indicated dates.

If there is, what would be the best method to use php to count users or perform this by mysql?

You can do this through PHP yes, both with the library Mysqli how much PDO.

Example with PDO:

<?php

$conn = new PDO("mysql:dbname=NOME-DO-BANCO-DE-DADOS;host=localhost", "DB-USUARIO", "DB-SENHA");

$stmt = $conn->prepare("SELECT `id`, COUNT(`id`) AS total FROM `uploads` WHERE `date_added` BETWEEN '2018-02-20 00:00:00' AND '2018-02-22 00:00:00' GROUP BY `ID`");
$stmt->execute();

var_dump( $stmt->fetchAll() );

Example with Mysqli:

<?php

$conn = new MySQLi("localhost", "DB-USUARIO", "DB-SENHA", "NOME-DO-BANCO-DE-DADOS");

$result = $conn->query("SELECT `id`, COUNT(`id`) AS total FROM `uploads` WHERE `date_added` BETWEEN '2018-02-20 00:00:00' AND '2018-02-22 00:00:00' GROUP BY `ID`");

while ($row = $result->fetch_assoc()) {
    var_dump( $row );
}

Structure used in the example:

+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| id         | int(11)          | NO   |     | NULL    |       |
| file_id    | int(10) unsigned | NO   |     | NULL    |       |
| date_added | datetime         | NO   |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+

Demonstration