Grouping results by multiple variables

[JakeTheSnake]

Hello, I'm new here, but am really impressed with the positive attitude! I was wondering if someone could give me some help or point me in the right direction. I have a query that returns the following fields:

city
state
name
serial
category

using this code
[PHP]

$p = '';
$result = mysql_query("SELECT city, state, title, serial, category FROM serials ORDER BY serial, category");

while($row = mysql_fetch_assoc($result))
{
if($row['serial'] == $p)
{
echo "<br />";
$p = $row['serial'];

}else{

echo "<br />";
echo "<br />";
$p = $row['serial'];
}
echo $row["city"];
echo $row["state"];
echo $row["title"];
echo $row["serial"];
echo $row["category"];

}

?>
[/PHP]

This generates the following:

Toronto     Ontario     Chef     12345     ABC
Detroit     Michigan     Cook      12345  DBF
 
Toronto     Ontario     Cook     12353     ABC
 
Los Angeles California     Waiter  12355  DGG
 
NYC         New York     Janitor 12382  DGG
 
Miami           Florida     Cook     12777     FGH
Fort Worth     Texas         Usher      12777  QWE
 
Dallas            Texas       Cook       22222  FGH
 
NYC         New York     Chef      33333  JKL
Detroit     Michigan     Janitor 33333     DBF
 

I'm not too concerned about the formatting of the results right now.

What I would like to do is to group like results together. Naturally the <serialnum> values are grouped together and broken up by the double line break. What I would like to also group like 'categories' together with the <serialnum> groupings.

Ideally the data above would be represented like this:

Toronto     Ontario     Chef     12345     ABC
Detroit     Michigan     Cook      12345  DBF
Toronto     Ontario     Cook     12353     ABC
Detroit     Michigan     Janitor 33333     DBF
NYC         New York     Chef      33333  JKL
 
Los Angeles California     Waiter  12355  DGG
NYC         New York     Janitor 12382     DGG
 
Miami           Florida     Cook     12777     FGH
Fort Worth     Texas         Usher      12777  QWE
Dallas            Texas       Cook       22222     FGH
 

So because the <category> field on line 3 matches the <category> field of line 1, it is grouped with the first grouping. Likewise, although the <serial> field of line 4 doesn't match either of lines 1,2,3 the <category> field of line 4 matches that of line 2. Lastly, since the <serial> field of line 5 matches the <serial> field of line 4, it is grouped in the first grouping.

Does this make sense? Sorry, reading it back it doesn't read that well. Let me know if I can make any further clarification.

Most importantly, thanks in advance for any direction that you can provide!

Jake

[pbmods]

Heya, Jake. Welcome to TSDN!

Looking at this part of your code:

if($row['serial'] == $p)
{
    echo "<br />";        // Note: similar line in else...
    $p = $row['serial'];  // Note: similar line in else...
}
else
{
    echo "<br />";
    echo "<br />";       // Similar line 1
    $p = $row['serial']; // Similar line 2
}
 

Notice that you're really only doing anything if the current value *doesn't* match the previous value. Have a look at this:

$lastSerial = '';
$lastCat = '';
 
.
.
.
    // Note that we always echo at least one <br />.
    echo '<br />';
 
    // If the current row s/b in a new section, add a <br />.
    if(! (($row['serial'] == $lastSerial) || ($row['category'] == $lastCategory)))
    {
        echo '<br />';
    }
 
    $lastSerial = $row['serial'];
    $lastCat = $row['cat'];
 

[JakeTheSnake]

Thanks for the warm welcome pbmods!

That definitely cleaned up the code, thank you.

The main thing I'd like it to do is like to put the results into groups based on either variable, <serial> or <category>. So in the first 'text' example above, line 16 should be placed up in the first grouping because the category 'DBF' is already present in the first grouping (line 2).

Hmmm still not too sure I made my question very clear.

Thanks for the quick response, it's great here!

Jake

[JakeTheSnake]

Hope this is allowed, but I just wanted to bump this and see if anyone had even heard of something like this being capable?

Thanks again!

Jake

[jx2]

well its exist solution for that :-)
but you actuly asked two questions pbmods answer you one of them

the other answer is here

it probably doesnt solve all problems but you get what you want to

look at "GROUP BY WITH ROLLUP"

[pbmods]

Heya, Jake.

Bumping is allowed on this site. NB as a result, we are extra-hard on double-posting (posting the same problem twice in the same forum).

I'm not sure I understand entirely what you're looking for. If you look at the example you provided:

Toronto     Ontario     Chef    12345  ABC
Detroit     Michigan    Cook    12345  DBF
Toronto     Ontario     Cook    12353  ABC
Detroit     Michigan    Janitor 33333  DBF
NYC         New York    Chef    33333  JKL
 
Los Angeles California  Waiter  12355  DGG
NYC         New York    Janitor 12382  DGG
 
Miami       Florida     Cook    12777  FGH
Fort Worth  Texas       Usher   12777  QWE
Dallas      Texas       Cook    22222  FGH
 

There's a definite pattern here; between any two rows in the same section, either the serial number is the same, or the category is the same. It seems like (and correct me if I'm wrong) MySQL is returning the results in the proper order, so the logical way to check to see if we need to start a new section would be to compare the current row with the previous one and see if both the serial and the category are different.

Would there be a situation where at least one of the criteria matches, but we should start a new section anyway? And/or are there circumstances where even though neither the serial nor the category matches, but we should still keep the two rows in the same section?

[JakeTheSnake]

jx2 - Thanks for your reply. As I understand the GROUP BY parameter (including GROUP BY WITH ROLLUP) it will only work if there is an aggregate function in the query. Since I want all lines to be echo'd I don't want to have an aggregate function.

pbmods - Yes and No, but mostly yes. :)

There's a definite pattern here; between any two rows in the same section, either the serial number is the same, or the category is the same.

Yes, thats exactly right. In the following example (as used previously) there are 3 groups, lets call them A, B, C. In group A line 4 doesn't have either the same serial or category as the line directly above it. It does however have the same category as another record from that that group, line 2.

Toronto     Ontario     Chef    12345  ABC
Detroit     Michigan    Cook    12345  DBF
Toronto     Ontario     Cook    12353  ABC
Detroit     Michigan    Janitor 33333  DBF
NYC         New York    Chef    33333  JKL
 
Los Angeles California  Waiter  12355  DGG
NYC         New York    Janitor 12382  DGG
 
Miami       Florida     Cook    12777  FGH
Fort Worth  Texas       Usher   12777  QWE
Dallas      Texas       Cook    22222  FGH 

Similarly, in Group C, line 12 doesn't have the same serial or category as the line above it, but the category matches that of line 10.

It seems like (and correct me if I'm wrong) MySQL is returning the results in the proper order

Not quite. The results are set to ORDER BY serial, which means that they'll all be grouped together, but the category column is not grouped together.

If I just use the following code as I currently am I could get 5000 results, and while they're sorted by serial, result/line 126 could have the same category as result 3456. Because line 126 has the same category as line 3456 they should be grouped together.

[PHP]
$result = mysql_query("SELECT city, state, title, serial, category FROM serials ORDER BY serial, category");
[/PHP]

Would there be a situation where at least one of the criteria matches, but we should start a new section anyway?

No.

And/or are there circumstances where even though neither the serial nor the category matches, but we should still keep the two rows in the same section?

No.

I just can't seem to wrap my head around how you'd group results based on either of two variables.

Thanks soooo much for your help so far!

[pbmods]

Heya Jake.

Ok. I see what you're getting at now; I was mistaking the name column for the category column.

Possibly part of the misunderstanding is that in MySQL, the phrase 'GROUP BY' has a slightly different meaning.

I think in your case, you'll need to manually parse your results and organize them that way:

$_groups = array();
$_idx = array();
while($_row = mysql_fetch_assoc($_res))
{
    if(isset($_idx['serial'][$_row['serial']]))
    {
        $_groups[$_idx['serial'][$_row['serial']]][] = $_row;
        $_idx['category'][$_row['category']] = $_idx['serial'][$_row['serial']];
    }
    elseif(isset($_idx['category'][$_row['category']]))
    {
        $_groups[$_idx['category'][$_row['category']]][] = $_row;
        $_idx['serial'][$_row['serial']] = $_idx['category'][$_row['category']];
    }
    else
    {
        $_groups[][] = $_row;
        end($_groups);
        $_idx['serial'][$_row['serial']] = key($_groups);
        $_idx['category'][$_row['category']] = key($_groups);
    }
}
 
foreach($_groups as $_entries)
{
    foreach($_entries as $entry)
    {
        foreach($entry as $val)
        {
            echo "$val\t";
        }
        echo '<br />';
    }
    echo '<br />';
}
 

Phew! Ok, so what's going on here?

What we're doing is creating a big multi-dimensional array. As we go through, we check to see if we've already processed an entry with the same serial or category. If we have, we assign it to the same group as the last one. Otherwise, we create a new group and update our index.

Going back to everybody's favorite example:

Toronto* * *Ontario* * *Chef* * 12345* ABC
Detroit* * *Michigan* * Cook* * 12345* DBF
Toronto* * *Ontario* * *Cook* * 12353* ABC
Detroit* * *Michigan* * Janitor 33333* DBF
NYC* * * * *New York* * Chef* * 33333* JKL
*
Los Angeles California* Waiter* 12355* DGG
NYC* * * * *New York* * Janitor 12382* DGG
*
Miami* * * *Florida* * *Cook* * 12777* FGH
Fort Worth* Texas* * * *Usher* *12777* QWE
Dallas* * * Texas* * * *Cook* * 22222* FGH
 

We end up with a $_groups that looks something like this (abbreviated for space):

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [City] => Toronto
                    ...
                    [Serial] => 12345
                    [Cat] => ABC
                )
            [1] => Array
                (
                    [City] => Detroit
                    ...
                    [Serial] => 12345
                    [Cat] => DBF
                )
            .
            .
            .
        )
    [1] => Array
        (
            [0] => Array
                (
                    [City] => Los Angeles
                    ...
                    [Serial] => 12355
                    [Cat] => DGG
                )
            [1] => Array
                (
                    [City] => NYC
                    ...
                    [Serial] => 12382
                    [Cat] => DGG
                )
        )
    .
    .
    .
)
 

Once we've done all that hard work, the output part is rather trivial....

[JakeTheSnake]

Yes Yes Yes!

Thank you sooo much pbmods! I've tested this code on a big db and it worked flawlessly. I'm going to spend some time and make sure I understand exactly whats going on.

Thanks so much for all your help, not only in this thread, but also in everyones threads!

Jake

[pbmods]

Heya, Jake.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)