views:

341

answers:

3

Hello,

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?

+1  A: 

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.

beNi
Thanks for approving my solution :D :D
Adam Kiss
+1  A: 

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
  }

}
Alec
Thanks for saying my ideas are OK :D
Adam Kiss
A: 

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.

Jenkz
thank you for your response - I however haven't seen it when voted for answer +1 anyway :]
Adam Kiss
No problem, Alec's solution looks a little neater anyway. I need to learn to type faster!
Jenkz