Grouping results by multiple variables

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • JakeTheSnake
    New Member
    • Aug 2007
    • 5

    #1

    Grouping results by multiple variables

    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("SE LECT city, state, title, serial, category FROM serials ORDER BY serial, category");

    while($row = mysql_fetch_ass oc($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:
    Code:
    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:
    Code:
    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
    Recognized Expert Expert
    • Apr 2007
    • 5821

    #2
    Heya, Jake. Welcome to TSDN!

    Looking at this part of your code:
    [code=php]
    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
    }
    [/code]

    Notice that you're really only doing anything if the current value *doesn't* match the previous value. Have a look at this:
    [code=php]
    $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'];
    [/code]

    Comment

    • JakeTheSnake
      New Member
      • Aug 2007
      • 5

      #3
      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

      Comment

      • JakeTheSnake
        New Member
        • Aug 2007
        • 5

        #4
        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

        Comment

        • jx2
          New Member
          • Feb 2007
          • 228

          #5
          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"

          Comment

          • pbmods
            Recognized Expert Expert
            • Apr 2007
            • 5821

            #6
            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:

            Code:
            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?

            Comment

            • JakeTheSnake
              New Member
              • Aug 2007
              • 5

              #7
              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.

              Code:
              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("SE LECT 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!

              Comment

              • pbmods
                Recognized Expert Expert
                • Apr 2007
                • 5821

                #8
                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 misunderstandin g 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:
                [code=php]
                $_groups = array();
                $_idx = array();
                while($_row = mysql_fetch_ass oc($_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($_group s as $_entries)
                {
                foreach($_entri es as $entry)
                {
                foreach($entry as $val)
                {
                echo "$val\t";
                }
                echo '<br />';
                }
                echo '<br />';
                }
                [/code]

                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:
                Code:
                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):
                Code:
                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....

                Comment

                • JakeTheSnake
                  New Member
                  • Aug 2007
                  • 5

                  #9
                  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

                  Comment

                  • pbmods
                    Recognized Expert Expert
                    • Apr 2007
                    • 5821

                    #10
                    Heya, Jake.

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

                    Comment

                    Working...