PHP & MySQL: Using Group by for Categories

PHP & MYSQL: using group by for categories

I'd recommend just a simple query to fetch all the rows, sorted by category id. Output the category only if its value changes from the previous row.

<?php

$stmt = $pdo-> query("SELECT * FROM `myTable` ORDER BY categoryID");

$current_cat = null;
while ($row = $stmt->fetch()) {
  if ($row["categoryID"] != $current_cat) {
    $current_cat = $row["categoryID"];
    echo "Category #{$current_cat}\n";
  }
  echo $row["productName"] . "\n";
}

?>

Group by Category in MYSQL

Assuming you want the "top" Product per category, you would need to group on category and use an aggregation (Max in this case).

Note, that even if mysql allows to have "unaggregated" columns along with an aggregation like bellow, the result might not be unique (MSSQL for instance does not allow such a query due to the missing aggregation/grouping on productName):

SELECT 
    productName, -- unaggregated -> undetermined.
    productCategoryID, -- grouping-condition -> fine
    MAX((maxprice/minprice-1)*100) as PercentChange -- Aggregation -> fine
FROM products WHERE updatedDate > DATE_ADD(NOW(), INTERVAL -1 DAY)
    AND productCategoryID NOT IN (0,58,12,13) 
    AND (maxprice/minprice-1)*100) < '60' 
GROUP BY productCategoryID
ORDER BY PercentChange DESC LIMIT 10

Given the data is (simplified)

productName | productCategoryID | PercentChange
A             1                   50
B             1                   10
C             2                   40 
D             2                   40

only the productCategoryID and PercentChange are reliable.

The result might be

A | 1 | 50
C | 2 | 40

but due to the lacking aggregation on productName the following result might also be possible:

A | 1 | 50
D | 2 | 40

It WILL producte a single entry per category, but if two rows are equal with regards to their "grouping", the final result is not 100% predictable and therefore another approach should be taken.

You could - for example - group by name as well, and then programmatically filter out unwanted results:

SELECT 
    productName, -- grouping-condition -> fine
    productCategoryID, -- grouping-condition -> fine
    MAX((maxprice/minprice-1)*100) as PercentChange -- Aggregation -> fine
FROM products WHERE updatedDate > DATE_ADD(NOW(), INTERVAL -1 DAY)
    AND productCategoryID NOT IN (0,58,12,13) 
    AND (maxprice/minprice-1)*100) < '60' 
GROUP BY productName, productCategoryID
ORDER BY PercentChange DESC LIMIT 10

would result in

A | 1 | 50
C | 2 | 40
D | 2 | 40

Compared to a "non-grouped" query this would at least eliminate every entry that doesn't match MAX(PercentChange) (per name and category).

ps.: If you want the minimum price change, use MIN() obviously.

List records grouped by category name

Luckily you are using PDO which already has this functionality for you, that can do exactly what you want - group the data based on some column.

PDO can group results into the nested arrays, based on the first field selected. So you need to list your list id as the first field in the field list , and then get your rows using fetchAll() with aforementioned fetch mode:

$sql = "SELECT dl.list_id, dl.id, dl.date, dl.list_name, dls.*
  FROM driving_lists dl LEFT JOIN
     driving_list_shipments dls
 ON dl.list_id = dls.list_id
 ORDER BY dl.list_id";

$driving = $dbh->query($sql)->fetchAll(PDO::FETCH_GROUP);

and now you get a neat nested array where your rows are grouped by list id!

To make it output neatly you have to use two nested foreach operators

foreach ($driving as $list_id => $list_data)
{
     echo $list_data[0]['list_name']."<br />\n"; 
     foreach ($list_data as $row)
     {
         echo "whatever";
     }
}

How to group posts by category using PHP & MySql?

Like suggested by Charlotte, you could group your posts in an associative array, with the categories as keys.

<?php

// Connecting to database

$categories = array(); // Create an empty array
while ($post = mysqli_fetch_array($result)) {
    if(!array_key_exists($post['category'], $categories)) { // Check if the category is already present
        $categories[$post['category']] = array(); // Create an array indexed with the category name
    }
    $categories[$post['category']][] = $post; // Add the post to the category
}
?>

Now you have to iterate twice : to display the categories and to display each post in the category.
When I have to deal with nested foreach to render html, I preferably use inline foreach.

The rendering of the posts should look like :

<?php foreach ($categories as $category => $posts) : ?>
    <h2><?php echo $category; ?></h2>
    <?php foreach ($posts as $post) : ?>
        <a href='article.php?id=<?php echo $post['post_id']; ?>' ><?php echo $post['title']; ?></a>
        <p>Posted on <?php echo date('d-m-y h:i:s',strtotime( $post['posted'] ) );?> In <a href='category5.php?id=<?php echo $post['category_id']; ?>' ><?php echo $post['category']; ?></a>
        </p>
    <?php endforeach; ?>
    <hr/>
<?php endforeach; ?>

Edit: I ometted the opening and closing php tags in the first block of code.
If you assemble the two blocks of code in your php file, it should work.

Grouping MySQL Output by Category

First, change your query so that you order your results by the Link_Category, then by the Link_Text.

SELECT * FROM {$table} ORDER BY Link_Category ASC, Link_Text ASC

Then when you loop over your results, put your <div class="linkcontent_title">Category Title</div> inside your loop. To change the Link_Category when the category changes, just use a simple php var that stores the current category, ie. $currentCategory

<body>
 <?php

   $currentCategory = ""; //use this to change the Category Title

   while($row = mysql_fetch_array($result))
   {
        if($row['Link_Category'] != $currentCategory) // was wrong on the first draft
        {
           if($currentCategory != "") //if this is not the 1st, close the last <div>
           { ?>
            </div>
     <?php } // ends not 1st Category Title if{} ?>
            <div class="linkcontent">
              <div class="linkcontent_title"><?php echo $row['Link_Category']?></div>
  <?php $currentCategory = $row['Link_Category']; //Set the Current Category
        } // ends the Category Title if{} ?>
               <a href="<?php echo $row['Link_URL']?>" >
                   <img src="<?php echo $row['Link_Image']?>" alt="<?php echo $row['Link_Text']?>" width="175" height="175" border="0" />
               </a>
    <?php }?>
            </div>
</body>

want to get products group by category in mysql from multiple categories

I suggest you to use 2 queries:

$q1 = mysql_query("SELECT * FROM catrgory ORDER BY cat_name");
while($cat = mysql_fetch_assoc($q1)) {
  print "**|-----".$cat['cat_name']."-----|**<br>";
  $q2 = mysql_query("SELECT * FROM product WHERE Pid IN (SELECT product_id FROM product_category WHERE category_id = ".$cat['cat_id'].") ORDER BY Pname");
  while($prod = mysql_fetch_assoc($q2)) {
     print " |***".$prod['Pname']."<br>";
  }
}

--> Update -->

I gave the 2 queries solutions because i thought you wanted to show empty categories...

in that case, just use your query above and replace your "while" loop with this:

$title_shown ='';
while($row=mysql_fetch_array($comp_query)){
  if($row['category_name'] != $title_shown) {
      $title_shown = $row['category_name'];
      print "\n**|-----".$row['category_name']."-----|**\n";
  }
  print "|-".$row['product_name']."\n";
}

MySQL Group by category limit N from each category

The below query gives no more than two random rows from each category:

SELECT title, category
FROM (
  SELECT v.*,
     if( category = @last_cat,
         if( @last_cat:=category, @x:=@x+1,@x:=@x+1),
         if( @last_cat:=category, @x:=0,@x:=0)
     ) x
  FROM (SELECT @last_cat:=-9876, @x:=-91234) x,
       (SELECT * FROM videos ORDER BY category, rand()) v
) x
WHERE x < 2

demo: http://sqlfiddle.com/#!2/59cf9/8

display table group by category using php

You could do something like this:

outside while loop:

$lastReasonId = '';

inside while loop:

if($rowfet['reasonid'] != $lastReasonId){
    // show heading
}
$lastReasonId = $rowfet['reasonid'];

But your code is far from ok. The $i++ thing is not doing anything, you should probably echo $rowfet['studentid'] there.
And you are using deprecated mysql code.

---

Related Topics