CODEWITHSUNDEEP
phpmysqlsql
papelucho
papelucho

Reputation: 367

Group rows by field

I have the following table structure:

+------------------------+
| id |  name  | category |
+------------------------+
|  1 | name_1 |    cat_1 |
|  2 | name_2 |    cat_2 |
|  3 | name_3 |    cat_1 |
|  . |   .    |      .   |
|  . |   .    |      .   |
|  n | name_n |    cat_k |
+------------------------+

were "n" is the total rows of table and "k" is an arbitrary number. My question is, is there any way to make an SQL query that retrieve rows grouped by category? I mean it is possible to get something like this structure?

array(
    "cat_1" => array(
                     "name_1", "1",
                     "name_3", "3",
               ),
    "cat_2" => array(
                     "name_2", "2",
                      some rows ....
               ),
    ...
    "cat_k" => array(
                     some rows....
               ),
)

If there is any way please give me some keywords, not entire solution please.

Upvotes: 0

Views: 63

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

The query itself will not give you that structure, but it is quite easy to read the result set into that sort of two dimensional array:

$query = "SELECT category, id, name FROM table ORDER BY category ASC, id ASC";


$result = /* use you DB query mechanism of choice here */;

$array = array();

while($row = /* use your DB row fetch mechanism of choice here */) {
    $array[$row['category']][] = array($row['id'] => $row['name']);
}

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

You can't really do this in a single query since mysql alone will not be able to yield multi-dimensional arrays, but it's almost trivial to do using PHP. Basically here is what you would do:

$cats = array();
while ($row = $result->fetch()) {
    if (!isset($cats[$row->category])) {
        $cats[$row->category] = array();
    }
    $cats[$row->category][] = $row->name;
}

Upvotes: 1

Related Questions