scotchegg78 Posted June 4, 2008 Share Posted June 4, 2008 Hi guys I believe I need to sort into a multi array offices and regions addresses, Regions are offices(sort of a HQ), and manage the area offices. e.g. tables cr_region(regionID,address1,address2,town,...) cr_office(officeID,regionID,address1,address2,town...) simple stuff really! Anyway whats the best way to gt this into an array so that i can later set things up so when someone hovers over a map the relevant region office details and its sub office details pop up. ? I will tackle the magin bit with JS, but a littel unsure of the array side of things for the data arrangement? any help or advice on creating my 2/3D php array of regions and offices? thanks for any input Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 4, 2008 Share Posted June 4, 2008 I think i know what you want, if so its actually pretty simple: <?php $array = array(); $sql = "SELECT * FROM cr_office"; $result = mysql_query($sql); while($row = mysql_fetch_assoc($result)){ $array[$row['regionID']][] = $row; } echo '<pre>'.print_r($array,1).'</pre>'; ?> Quote Link to comment Share on other sites More sharing options...
scotchegg78 Posted June 4, 2008 Author Share Posted June 4, 2008 HI thanks for the quick reply ginger. That does get the offices sorted via regions nicely thanks. Just wondering if if there is a nice way to add the regions addresses to this array aswell? So its arranged under each regionID the region addresses and then the array of offices? You with me? thanks again Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 4, 2008 Share Posted June 4, 2008 Yeah, it's more complicated. This *should* do the trick, untested though: <?php $array = array(); $sql = "SELECT o.*,r.* FROM cr_office as o,cr_region as r WHERE o.regionID=r.regionID ORDER BY o.regionID"; $result = mysql_query($sql) or die(mysql_error()); $lastid = ''; while($row = mysql_fetch_assoc($resut)){ if($lastid != $row['r.regionID']){ $array['r.regionid'] = array('r.address1'=>$row['r.address1'],'r.address2'=>$row['r.address2']); //fill in with all other fields from region table (e.g. r.*); $lastid = $row['r.regionID']; } $array['r.regionid']['offices'][$row['o.officeID']] = array('o.address1'=>$row['o.address1'],'o.address2'=>$row['o.address2']); //fill in with all other fields from office table (e.g. o.*); } echo '<pre>'.print_r($array,1).'</pre>'; ?> Quote Link to comment Share on other sites More sharing options...
scotchegg78 Posted June 4, 2008 Author Share Posted June 4, 2008 thanks again ginger. I am playing with it but get this... Array ( [r.regionid] => Array ( [offices] => Array ( [] => Array ( [o.address1] => [o.address2] => ) ) ) ) the SQL seems to work and returns the 58 offce rows, but then adds the region details to every row, is this right? Had to modify your code a little, to this.. dont think i can see an error.. $sql = "SELECT o.*,r.* FROM cr_office as o,cr_region as r WHERE o.regionID=r.regionID ORDER BY o.regionID"; $result = $dblink->query($sql); $lastid = ''; while($row = $result->fetch_assoc()){ if($lastid != $row['r.regionID']){ $array['r.regionid'] = array('r.address1'=>$row['r.address1'],'r.address2'=>$row['r.address2']); //fill in with all other fields from region table (e.g. r.*); $lastid = $row['r.regionID']; } $array['r.regionid']['offices'][$row['o.officeID']] = array('o.address1'=>$row['o.address1'],'o.address2'=>$row['o.address2']); //fill in with all other fields from office table (e.g. o.*); } echo '<pre>'.print_r($array,1).'</pre>'; Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 4, 2008 Share Posted June 4, 2008 Hmm, i thought the table prefixes would be preserved in the selected column names, but apparently not. Looks like you'll have to add all of the fields form one of the tables with a prefix manually (i've started this with the office table, adding o before each field name). <?php $array = array(); $sql = "SELECT o.address1 as oaddress1,o.address2 as oaddress2, o.officeID as oofficeID, r.* FROM cr_office as o,cr_region as r WHERE o.regionID=r.regionID ORDER BY r.regionID"; //add tables from office and add a prefix of o to each field name $result = mysql_query($sql) or die(mysql_error()); $lastid = ''; while($row = mysql_fetch_assoc($result)){ if($lastid != $row['regionID']){ $array[$row['regionID']] = array('address1'=>$row['address1'],'address2'=>$row['address2']); //fill in with all other fields from region table - note: no 'r.' required $lastid = $row['regionID']; } $array[$row['regionID']]['offices'][$row['oofficeID']] = array('oaddress1'=>$row['oaddress1'],'oaddress2'=>$row['oaddress2']); //fill in with all other fields from office table (e.g. o.*); } echo '<pre>'.print_r($array,1).'</pre>'; ?> Quote Link to comment Share on other sites More sharing options...
scotchegg78 Posted June 4, 2008 Author Share Posted June 4, 2008 Many Many thanks ginger! Seesm to work out right. I susepct i could of done it alot simplier using php to sort the data from simplier queries, but this should be great. Now just got to figure out how to handle the data to display on rollovers of a map region! lol Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 4, 2008 Share Posted June 4, 2008 No problem, glad its working. I susepct i could of done it alot simplier using php to sort the data from simplier queries, but this should be great. Yeah, but why use lots of queries when you can ust just the one? If you'll all done, can you mark the topic as solved? Quote Link to comment Share on other sites More sharing options...
scotchegg78 Posted June 4, 2008 Author Share Posted June 4, 2008 Done. Can I ask a question Ben, What motivates you guys to help people out on here? You must i of saved so many people in the past, its amazing when you think about it. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 4, 2008 Share Posted June 4, 2008 Well i could get all philosophical on you and suggest that there's no such thing as altruism (e.g. helping for no personal gain), seeing as i think everybody who does help gets some sense of achievement out out it --it's nice to know you've helped someone. There's also the fact that I learn a lot from helping others. The vast majority of stuff that i've learnt about PHP has been through this site, be it reading others' posts or finding out how to do something for myself. The questions asked expose you to things you might otherwise not have thought about/encounted, so I think you learn at lot. Also, I think it's true to say that until you can explain a concept to somewhere else, it's still debatable wether or not you truly understand it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.