LIKE with COUNT in PHP

Question

I can’t understand what’s going wrong with the code:

$query="SELECT aluno FROM bonesmirn2 WHERE data LIKE '%2018-09%' AS faltas  GROUP BY aluno HAVING COUNT(aluno) > 0 ORDER BY COUNT(aluno) DESC";


$result = mysqli_query($dbcon, $query);
$number_of_rows = mysqli_num_rows($result);

$response = array();

if($number_of_rows > 0){
    while($row = mysqli_fetch_assoc($result)){
        $response[] = $row;
    }
}
echo json_encode(array("lista"=>$response));
mysqli_close($dbcon);

Answer 1

And this AS faltas does not mean anything because it is giving nickname to a filter field.

Use LIKE for data, is a "gambiarra", and that takes more work than doing right.

---

How to work on dates

Use the functions YEAR, MONTH, DAY to fetch the value of YEAR, MONTH, DAY country data:

SELECT aluno FROM bonesmirn2 
WHERE YEAR(data) = '2018' AND MONTH(data) = '09'

If you have more than 1 value:

SELECT aluno FROM bonesmirn2 
WHERE YEAR(data) = '2018' AND MONTH(data) IN ('07','09','11')

If you want a intermission sequential:

SELECT aluno FROM bonesmirn2 
WHERE YEAR(data) = '2018' AND MONTH(data) BETWEEN '05' AND '12'

---

How to use the count

You need to have something to "count". Even if this table has only 1 record of each student, the count will always be greater than 1, then its null query with HAVING COUNT(aluno) > 0.

Example

You could count how many times the student appears in the table, so create a parameter.

For example, if this table bonesmirn2 is a record of attendance, we will bring only students who had more than 5 presence:

SELECT aluno, COUNT(aluno) as presenca
FROM bonesmirn2 
WHERE YEAR(data) = '2018' AND MONTH(data) = '09'  
GROUP BY aluno
HAVING COUNT(aluno) > 5 
ORDER BY COUNT(aluno) DESC

---

Useful links

Date and Time Functions

DISTINCT and GROUP BY, what is the difference between the two statements?

SQL HAVING Clause