PHP/MySQL group results by column

Question

in order to keep as few SQL statements as possible, I want to do select set from MySQL:

SELECT * FROM products WHERE category IN (10,120,150,500) ORDER BY category,id;

Now, I have list of products in following manner:

CATEGORY
 - product 1
 - product 2
CATEGORY 2
 - product 37
...

What's the best and most efficent way to process MySQL result?

I thought something like (pseudo PHP)

foreach ($product = fetch__assoc($result)){
  $products[$category][] = $product;
}

and then when outputting it, do foreach loop:

foreach($categories as $category){
  foreach($products[$category] as $product){
    $output;
  }
}

Is this the best, or is something magical like mysql_use_groupby or something?

When you GROUP BY category, you only get one product per category in the result set. Is that what you want? — mluebke
– mluebke, Commented Feb 26, 2010 at 18:31
@mlueabke: thank you, I edited my SQL statement with proper grouping - another ORDER instead of GROUP BY — Adam Kiss
– Adam Kiss, Commented Feb 26, 2010 at 21:46

Alec · Accepted Answer · 2010-02-26 21:44:55Z

3

Like mluebke commented, using GROUP means that you only get one result for each category. Based on the list you gave as an example, I think you want something like this:

$sql = "SELECT * FROM products WHERE category IN (10,120,150,500) GROUP BY category ORDER BY category, id";
$res = mysql_query($sql);

$list = array();
while ($r = mysql_fetch_object($res)) {
  $list[$r->category][$r->id]['name'] = $r->name;
  $list[$r->category][$r->id]['whatever'] = $r->whatever;
  // etc
}

And then loop through the array. Example:

foreach ($list as $category => $products) {
  echo '<h1>' . $category . '</h1>';

  foreach ($products as $productId => $productInfo) {
    echo 'Product ' . $productId . ': ' . $productInfo['name'];
    // etc
  }

}

answered Feb 26, 2010 at 21:44

Alec

9,1089 gold badges41 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bflesch · Accepted Answer · 2010-02-26 18:21:42Z

2

Nope, I think your solution is the best for this problem. It seems that what's important for you is the output later on, so you should stick with your approach.

answered Feb 26, 2010 at 18:21

bflesch

5834 silver badges6 bronze badges

Comments

Jon M · Accepted Answer · 2010-02-26 21:50:00Z

0

Do you want to get a list of categories or actually get all products grouped into categories?

If it's the latter, best to do:

SELECT 
p.product_id, 
p.name, 
p.category_id, 
c.name AS category 
FROM products p 
JOIN categories c ON (c.category_id = p.category_id AND p.category_id IN (x,y,z))

Then in PHP you can go through the array (psuedo code):

    $cats = array();

    foreach($products as $product) { 
        if(!in_array($product['category'], $cats)) {
            $cats[$product['category_id']] = $product['category'];
        }
        $cats[$product['category_id']][$product['product_id']] = $product['name'];
    }

Which will leave you with $cats as an array with products sorted into it.

answered Feb 26, 2010 at 21:50

Jon M

4642 gold badges7 silver badges21 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL group results by column

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related