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

Question

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

Navigation

#1 by (1 votes)

1

I have tbl_ocorrencias that 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 instance only has one vehicle but a vehicle can have multiple occurrences.

What I want is in the following image:

What's in the image is this:

I can only fetch the last 3 occurrences of a given id_viatura
Each occurrence found is listed in a forward column as it appears in the image

For this I am trying to do in php and mysql but I am not able to come up with a solution.

php mysql sql

asked by anonymous 07.08.2014 / 11:44

1 answer

Retrieving MultiSelectList MVC values Validation with Toast and insertion in the bank

score 1 · Accepted Answer

$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 line change and% change% column change. That's how I solved my question.