How to count the last records that fit a condition?

Question

How to count the last records that fit a condition?

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

0

I want to count the last records when they meet that:

its estado equals 1

if the usuario_id equals 5, and

the test_id equals 1

The maximum number of records to count is only 3.

These records I have in a table with the following structure:

I tried count () and limit like this:

public function cuenta($usuarioID,$testID){
    $consulta = "SELECT COUNT(*) AS total 
                   FROM (SELECT 1 
                           FROM repuestas 
                          WHERE estado = 1 
                            AND usuario_id = '$usuarioID' 
                            AND test_id = '$testID'
                          ORDER BY id DESC 
                          LIMIT 3,3) t";

    $resultado = $this->_db->query($consulta) or die(print_r($this->_db->errorInfo()));
    return $resultado->fetch(PDO::FETCH_ASSOC);
}

The problem with this query is that it counts all those who have been in 1, and that would give 3 (for limit 3).

But if you look at the image in the last 3 records only 44 has been in 1 , and that should throw me:

that among the last 3, only 1 has been in 1.

mysql php pdo

asked by jsoe jose 30.10.2016 в 00:48

source

3 answers

1

The query will always result in 3 because you are using estado = 1 as a filter. However much you use limit 3 you will get the last three records that have estado = 1 , that is, those that have id 44, 43 and 42 since you do not take into account the records that have estado = 0 .

Simply remove estado = 1 from your query.

Also, another error that I found in your query is the LIMIT you have. You have it as LIMIT 3,3 or what is the same, LIMIT 3 OFFSET 3 . Therefore, your query will take the last three records starting with the fourth by the end (taking ids 43, 42 and 41, which have estado = 1 ).

To fix it completely just put LIMIT 3 .

Your final query would look like:

$consulta = "SELECT COUNT(*) AS total FROM (SELECT 1 FROM repuestas WHERE usuario_id = '$usuarioID' AND test_id = '$testID' ORDER BY id DESC LIMIT 3) t";

answered by 30.10.2016 в 01:46

0

The problem I have is that it does not count the last three records if not the last 3 that his state is 1 and he throws that I have 3 when in reality should throw 1. from now thank you very much for the help that can provide: D

Then delete WHERE estado = 1 Since you are indicating in the query that you only consider records with status equal to 1.

answered by 30.10.2016 в 01:39

configure sql express Error Selecting Records with EntityFramework

score 2 · Accepted Answer

What I understand is that you want to find the last 3 records for a specific combination of usuario_id and test_id . And, then, from among those last 3 records, you want to count how many have estado_id = 1 .

If I understood the question correctly, then this is the SQL query you need:

select count(case when estado_id = 1 then 'X' end)
  from (select estado_id
          from repuestas
         where usuario_id = 5 -- aquí usarías $usuarioID, estoy usando 5 como ejemplo.
           and test_id = 1 -- aquí usarías $testID, estoy usando 1 como ejemplo.
         order by id desc
         limit 3) t

or this is also equivalent:

select count(*)
  from (select estado_id
          from repuestas
         where usuario_id = 5 -- aquí usarías $usuarioID, estoy usando 5 como ejemplo.
           and test_id = 1 -- aquí usarías $testID, estoy usando 1 como ejemplo.
         order by id desc
         limit 3) t
 where estado_id = 1

SQL integrated with your PHP code

public function cuenta($usuarioID,$testID){
    $consulta = "select count(case when estado = 1 then 'X' end) as total 
                   from (select estado_id
                           from repuestas 
                          where usuario_id = '$usuarioID' 
                            and test_id = '$testID'
                          order by id desc
                          limit 3) t";

    $resultado = $this->_db->query($consulta) or die(print_r($this->_db->errorInfo()));
    return $resultado->fetch(PDO::FETCH_ASSOC);
}

o ...

public function cuenta($usuarioID,$testID){
    $consulta = "select count(*) as total 
                   from (select estado_id
                           from repuestas 
                          where usuario_id = '$usuarioID' 
                            and test_id = '$testID'
                          order by id desc
                          limit 3) t
                  where estado_id = 1";

    $resultado = $this->_db->query($consulta) or die(print_r($this->_db->errorInfo()));
    return $resultado->fetch(PDO::FETCH_ASSOC);
}