[SOLVED] Sorting offices and regions into 3D Array ?

[scotchegg78]

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

 

[GingerRobot]

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>';
?>

 

[scotchegg78]

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

[GingerRobot]

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>';
?>

[scotchegg78]

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>';

 

[GingerRobot]

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>';

?>

[scotchegg78]

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

[GingerRobot]

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?

 

 

[scotchegg78]

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.

[GingerRobot]

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.