handeling many to many results

[jixor]

When using normalization for many to many is there a way I can write my sql to make it rather than repeating results (F1 for one to many) to put the 'many' in a sub array so i get (F2) so item_id 2 is fine because it has only one category, but item_id 1 has a lot of unecessary data being repeated for each category.

F1

array( [0] => array (
  [item_id] => 1 [category_id] => 1 )
[1] => array (
  [item_id] => 1 [category_id] => 2 )
[2] => array (
  [item_id] => 2 [category_id] => 1 )

F2

array( [0] => array (
  [item_id] => 1 [category_id] => array( [0] => 1 [1] => 2) )
[1] => array (
  [item_id] => 2 [category_id] => array( [0] => 1) )

F3

SELECT item.item_id, category.category_id FROM item, item2cat, category WHERE item.item_id = item2cat.item_id AND item2cat.category_id = category.category_id

Thanks.

By the way I have been playing around with various sql but it hasn't worked well.

Obviously I have simplified the real life version as that is massive.

Or do I just have to write a function to join the results together?

[eoghain]

There is no way to do this in SQL. You will have to take all of your rows and do processing on them to build the array structures that you want.