softboxkid softboxkid - 6 months ago 20
PHP Question

Group MySQL Result by column

I have a problem on output the MySQL result to HTML. I had a MySQL result which returns:

| title | category_name | from_where |
---------------------------------------------
| Title num 1 | Journal | iidl_file |
---------------------------------------------
| Title num 2 | Books | iidl_file |
---------------------------------------------
| Title num 3 | Announcement | iidl_post |
---------------------------------------------
| Title num 4 | News | iidl_post |
---------------------------------------------


How can I preview this data in HTML like this:

iidl_file
- Title num 1 (Journal)
- Title num 2 (Books)

--------------------------

iid_post
- Title num 3 (Announcement)
- Title num 4 (News)

Answer

This is what I would do:

SELECT `title`, `category_name`, `etc` FROM `table_name` ORDER BY `from_where`;

If you have many records, you will benefit greatly from making sure there is an index on the from_where column.

Here is some pseudo-code:

$groups = array();
foreach ($rows as $row) {
    $from_where = $row['from_where']
    if (! isset($groups[$from_where])) {
        $groups[$from_where] = array();
    }
    $groups[$from_where][] = $row;
}

Now your $groups will contain arrays keyed by the from_where column.

Doing this, you actually don't have to even issue the ORDER BY in the query.