Is there an proper way to structure the array better for looping

Question

OK so I have this page and as you can see I am loggering the array of 12 products. If you scroll down you can see the requirements are that i need to group them by category name but i am not sure the best way to structure the loops.

my query

$query = "SELECT p.name, p.price, pc.quantity, p.image, p.descr, t.name as Category_name
          FROM products as p
          join categorizations as pc on p.id = pc.product_id
          join categories as c on c.id = pc.category_id
          JOIN types AS t on t.id = p.type_id
          WHERE  c.code      = '{$type}_{$count}'
          order by p.order desc";

$allproducts = array();
while($row_r = mysql_fetch_assoc($result)){
  $allproducts[] = $row_r;
}

maybe i need to change my query or something to group by category name but that might drop some products. maybe i can create a multidemensional array and loop each category name

any suggestions

Answer 1

Is there a unique column among the ones you're pulling? If so, you can GROUP BY both Category_name and it, which will group the categories together but avoid losing any rows to it. You would then have your data organized by category, which would be easier to loop over, the database having done the bulk of the organizational work for you.

If you don't have a unique column you're already pulling, it can't hurt much to add the primary key column. In that case, your GROUP BY would probably look like:

GROUP BY Category_name, p.id

Answer 2

If you change your array line from,

while($row_r = mysql_fetch_assoc($result)){
  $allproducts[] = $row_r;
}

to

while($row_r = mysql_fetch_assoc($result)){
  $allproducts[$row_r['Category_name']][$row_r['name']] = $row_r;
}

You'll be grouping the items by the category automatically, it depends on how you use the information afterwards.

If you wish to tidy up your array, to reduce the memory footprint of the array, you can add these lines into the loop:

  unset($allproducts[$row_r['Category_name']][$row_r['name']]['Category_name']);
  unset($allproducts[$row_r['Category_name']][$row_r['name']]['name']);

If that looks complicated or confusing, I normally use the OBJECT.

while($row_r = mysql_fetch_object($result)){
  $allproducts[$row_r->Category_name][$row_r->name] = $row_r;
  unset($allproducts[$row_r->Category_name][$row_r->name]['Category_name']);
  unset($allproducts[$row_r->Category_name][$row_r->name]['name']);
}