Result Grouped by Field and Listing N Records

Asked

Viewed 17 times

0

I have a table called area with the structure:

Id, Nome, Status

I have another table called news with the structure:

Id,Título,Descricao,Data,IdArea,Status

I would like to return all news records grouped by area code, where the result would be grouped by Area Id, and list N news.

Format of the desired result:

  [Id Área] 
    [idNoticia],[título],[descricao]
    [idNoticia],[título],[descricao]
    [idNoticia],[título],[descricao]
  [...]
  [...]
php mysql

1 answer

1


Run this query using the driver you want (PDO, mysqli_* or mysql_*):

SELECT idarea, id, titulo, noticia FROM noticia ORDER BY idarea;

Assuming the result of this query is an array and is in the variable $resultado:

$formatado = [];

foreach ($resultado as $linha) {
    if (!$formatado[$linha["idarea"]]) {
        $formatado[$linha["idarea"]] = [];
    }

    $formatado[$linha["idarea"]][] = $linha;
}

You can adapt the above code to use with objects or numerical arrays

  • I want to see the possibility of the return being directly the database, without interaction with php for example. See if it is feasible or not too.

  • In your question it is not clear the need to format the result of the database, but it is not possible, there is no way to make queries that return multidimensional arrays

  • so in case you want to return all records of the table news for all areas, in case I will have to do more than one query to bd to be able to receive the data and manipulate them in php for example. The idea is to group all news by area, so you could manipulate the data better.

  • I guess I didn’t get it, I could explain it better?

  • worth, gave to understand his answer, is that wanted to sweep the table Area, and return grouped by Id of the area all the corresponding news, but wanted in a multidimencional array not to need to make n queries in the bank and return separately for each Idarea

  • In general you will hardly need to perform more than one query to show the data, but you may need to format, as in this case. One remark, this type of formatting is preferable to do on the frontend, to relieve the server

Show 1 more comment

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.