Count Number of Uploads in a Certain Period

Question

Count Number of Uploads in a Certain Period

Navigation

#1 by (0 votes)

0

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 looks like this:

In this case I have repeated user IDs because they have uparam more than one file and have gained point so my doubts are as follows:

1 - Would I have to do the count in a user IDs query only? 2 - If there is, what would be the best method to use php to count users or to do this by mysql?

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

php mysql

asked by anonymous 23.02.2018 / 00:15

1 answer

You can request a specific file for the user Wamp sitem in PHP with .htaccess problem for local access

score 0 · Accepted Answer

Would you like to do the counting on a user-only IDS query?

Yes, you can 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 count how many records that ID clustered have.

And to filter by date, just use the BETWEEN operator. . With this operator you can pass the start date and end 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 he will also count the number of uploads per user, but now he will filter between the dates indicated.

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

You can do this through PHP yes, both with MySQLI and

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    |       |
+------------+------------------+------+-----+---------+-------+

Demo