Search 3 results from the same table grouped by the same id

Question

I have the tbl_ocorrencias which records occurrences of a particular vehicle. That is, the table consists of the following:

- tbl_ocorrencias -
   id_ocorrencia
   id_viatura
   data
   hora

In this case, an incident has only one vehicle but one vehicle may have several occurrences.

What I want is in the following image:

What’s in the picture is as follows:

• I can only pick up the last 3 occurrences of a particular id_viatura
• Each occurrence found is listed in a column in front as shown in the image

For this I’m trying to do in php and mysql but I’m not getting a solution.

Answer 1

$result = mysql_query("SELECT * FROM tbl_ocorrencias GROUP BY id_viatura ORDER BY data, hora DESC",$conn);
while($row = mysql_fetch_assoc($result)) {
    $id_viatura = $row['id_viatura'];
    $sql1 = mysql_query("SELECT * FROM tbl_ocorrencias WHERE id_viatura = '$id_viatura' ORDER BY data, hora DESC LIMIT 3",$conn);
    while($row1 = mysql_fetch_assoc($sql1)) 
    {
        if($id_viatura_velho != $id_viatura)
        {
            $outstr.= "\n";
        }
        $outstr.= $row1['id_viatura'].";".$row1['data'].";".$row['hora'].";";
        $id_viatura_velho = $row1['id_viatura'];

    }
}

The \n represents a change of line and the ; column change. That’s how I solved my question