PHP / MySQL group column results

Question

so that as few SQL statements as possible, I want to make a choice from MySQL:

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

Now I have a list of products as follows:

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

What is the best and most efficient way to handle MySQL?

I thought something like (pseudo PHP)

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

and then, outputting it, execute the foreach loop:

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

Is it the best, or something magical, like mysql_use_groupbyor something else?

Answer 1

Like the comment mluebke, using GROUP means that you get only 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 scroll through the array. Example:

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

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

}

Answer 2

, , . , , .

Answer 3

Do you want to get a list of categories or actually get all the products grouped by category?

If this is the last, the best thing to do is:

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 an 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 $ cat as an array with products sorted in it.