PHP/MySQL group results by column

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?

Answers


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
  }

}

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.


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.


Need Your Help

Mysql DB Table Rows Disappearing

mysql

A really weird (for me) problem is occurring lately. In an application that accepts user submitted data the following occurs at random:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.