A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2008 yearPHP/mySQL - how to fetch nested rows into multidimensinal array - page 1
User InfoPosts
PHP/mySQL - how to fetch nested rows into multidimensinal array#1
Coming from another question of mine where I learnt not to EVER use db queries within loops I consequently have to learn how to fetch all the data in a convenient way before I loop through it.

Let's say I have two tables 'scales' and 'items'. Each item in items belongs to one scale in scales and is linked with a foreign key (scaleID). I want to fetch all that data into an array structure in one query such that the first dimension are all the scales with all the columns and nested within, all items of one scale all columns.

Result would be something like that:

scale 1, scaleParam1, scaleParam2, ...
....item1, itemParam1, itemParam2, ...
....item2, itemParam1, itemParam2, ...
scale 2, scaleParam2, scaleParam2, ...
....item1, itemParam1, itemParam2, ...
....item2, itemParam1, itemParam2, ...


So far I've done mainly left joins for one-to-one relationships. This is a one-to-many and I just can't wrap my mind around it.

Is it a right join, could it also be done with a subquery, how to get the full outer rows into it as well...

later I would like to iterate through it with to nested foreach loops.

Maybe it's just that I have a headache...

posted date: 2008-12-10 10:49:00


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2008-12-10 10:49:01


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#3
It might be easier to first get all the scales, then all the items.//first get scaleswhile ($row = fetchrowfunctionhere()) { $scale = $scales->createFromArray($row);}//then get items$lastId = null;while ($row = fetchrowfunctionhere()) { $scaleId = $row['scaleID']; if ($lastId != $scaleId) { $scale = $scales->getByScaleId($scaleId); } $item = $items->createFromArray($row); $scale->addItem($item); $lastId = $scaleId;}or everything in one sql$lastId = null;while ($row = fetchrowfunctionhere()) { $scaleData = array_slice($row, 0, 5, true); $itemData = array_slice($row, 5, 5, true); $scaleId = $scaleData['scaleID']; if ($lastId != $scaleId) { $scale = $scales->createFromArray($scaleData); } $item = $items->createFromArray($itemData); $scale->addItem($item); $lastId = $scaleId;}everything as one happy arraywhile ($row = fetchrowfunctionhere()) { $scaleData = array_slice($row, 0, 5, true); $itemData = array_slice($row, 5, 5, true); $scaleId = $scaleData['scaleID']; if (!isset($scales[$scaleId])) { $scales[$scaleId] = $scaleData; } $itemId = $itemData['itemID']; $scales[$scaleId]['items'][$itemId] = $itemData;}

posted date: 2008-12-10 11:19:00


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#4
The query should look something like this:SELECT * FROM scalesINNER JOIN items ON scales.id = items.scale_idIf you want to iterate through with nested loops, you'll need to pull this data into an array - hopefully you're not pulling back so much that it'll eat up too much memory.$scales = array();while ($row = mysql_fetch_assoc($data)){ if (!isset($scales[$row['scale_id']])) { $row['items'] = array(); $scales[$row['scale_id']] = $row; } $scales[$row['scale_id']]['items'][] = $row;}Then you can loop through:foreach ($scales as $scale){ foreach ($scale['items'] as $item) ; //... do stuff}Note: this is somewhat naive in that $scale and $item will both contain fields from BOTH tables... if that's a problem then you need to change the assignments in the loop above to pull out only the fields you want.

posted date: 2008-12-10 11:23:00


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#5
wow, you have loads of accepted answers, amazing. thanks for this one, could also end up on top.

posted date: 2008-12-10 11:31:00


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#6
I have to admit that I will have to study your solutions since I don't understand exactly what they do.

posted date: 2008-12-10 11:38:00


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#7
In relation to the question that spawned this one, and to your comment about eating up too much memory, I'd rather have a large array, let's say 1mb of RAM than to run many (dozens?) of queries.

posted date: 2008-12-10 12:29:00


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#8
Also, good job on using mysql_fetch_assoc, many people do mysql_fetch_array which is inefficient.

posted date: 2008-12-10 12:30:00


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#9
The first 2 are objects, which makes it easier. The 3rd is with arrays. You didnt specify mysql in your text (but I see the mysql tag now) so I wrote a generic function name instead of mysql_fetch_assoc.

posted date: 2008-12-10 13:10:00


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#10
In the meantime I've tried it and your solution is not entirely correct, at least in my scenario, the if (!isset($scales[$row['scale_id']])) part flaws the generation of a proper array-structure... See my solution in my own answer.

posted date: 2008-12-10 18:32:00


Re: PHP/mySQL - how to fetch nested rows into multidimensinal array#11
Oops, fixed missing ['items']

posted date: 2009-01-21 07:46:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.