Grouping related items in PHP

Question

I have some rows in a database which represent line items in a sale. Each line item has five columns:

• id : Primary key.
• sale_id : The sale that this line-item is a part of (a foreign key to a Sales table).
• product_id : The product this item corresponds to (a foreign key to a Products table).
• group : The line-items group that this item is a part of.
• is_subitem : Whether this line item is a subitem or not. There is guaranteed to be exactly one "false" value here for any group of line items that have the same group value.

I would like to iterate over all the rows for a particular sale_id to produce a list where:

• line items with identical group values are associated together
• the line item with is_subitem == false for that group appears first

How can I create a data structure which facilitates this and iterate the way I'd like?

Answer 1

I edited the above a few times but it should be pretty good now.

Answer 2

Something like this might work.. I haven't tested it.

$data = array();
$result = mysql_query( $your_query_here );

while( $row = mysql_fetch_assoc( $result ) )
{
    if( !isset( $data[$row['group']] ) )
    {
        $data[$row['group']] = array();
    }

    if( false == $row['is_subitem'] )
    {
        // when subitem is false we put the item at at the front of the array by unshifting it.
        array_unshift( $data[$row['group']], $row );
    }
    else
    {
        // else we just add the row to the end.
        $data[$row['group']][] = $row;
    }
}