I can not understand what's 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);
First of all, this AS faltas does not mean anything, as it is giving a nickname to a filter field.
Another thing, to use LIKE to date, is a "gambiarra."
Use the YEAR, MONTH, DAY functions that take YEAR, MONTH, DAY from the 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 sequential range:
SELECT aluno FROM bonesmirn2
WHERE YEAR(data) = '2018' AND MONTH(data) BETWEEN '05' AND '12'
count
You need something to "count".
Even if this table has only 1 record of each student, the count will always be greater than 1, so its query is null 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 bonesmirn2 table is a presence record, we will only bring students who have 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