PHP nested foreach statement...

Hello, I have a table as follows:

Region         State
West              AZ
West             TX
East               NY
East               NH
East               CT
West             UT

I am looking for an output as follows;

West : AZ,TX,UT
East ; NY, NH, CT

Can someone please let me know how I can do this?

Thanks,
A
aej1973Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
If this is a SQL database, I think you can use GROUP BY to get the results.
Mukesh YadavFull Stack DeveloperCommented:
Using php you can do it in following way:
Select all data from table
$required = array();
foreach($records as $record){
  $required[$record->Region][] = $record->State;
}

echo 'West : ' . implode(", ", $required["West"]);
echo 'East : ' . implode(", ", $required["East"]);

Open in new window

Ray PaseurCommented:
Something like this might work, too.  The choice of approaches depends on the format the original data.
http://iconoun.com/demo/temp_aej1973.php

<?php // demo/temp_aej1973.php

/**
 * http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28679777.html
 */
error_reporting(E_ALL);

$data = array(
['West' => 'AZ'],
['West' => 'TX'],
['East' => 'NY'],
['East' => 'NH'],
['East' => 'CT'],
['West' => 'UT'],
);

// AGGREGATE STATES BY REGION
$out = [];
foreach ($data as $arr)
{
    foreach ($arr as $region => $state)
    {
        $out[$region][] = $state;
    }
}

// CONVERT REGIONAL ARRAYS OF STATES INTO STRING REPRESENTATION
$str = NULL;
foreach ($out as $region => $arr)
{
    $str .= "$region: " . implode(',', $arr) . PHP_EOL;
}

// SHOW THE WORK PRODUCT
echo '<pre>';
var_dump($str);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Brian TaoSenior Business Solutions ConsultantCommented:
If you're using MySQL, you can use GROUP_CONCAT with GROUP BY like this:
SELECT CONCAT(Region, ': ', GROUP_CONCAT(State SEPARATOR ', ')) FROM the_table_you_have GROUP BY Region

Open in new window

aej1973Author Commented:
I am not sure, I seem to have  a problem here. I have  a db with the following data;

Region         State
West              AZ
West             TX
East               NY
East               NH
East               CT
West             UT

I am trying to get the following output in a table and I seem to be running into a problem;

<h1>West</h1>
<tr>
    <td>AZ</td>
   <td>TX</td>
   <td>UT</td>
</tr>

<h1>East</h1>
<tr>
    <td>NY</td>
   <td>CT</td>
   <td>NH</td>
</tr>

I have about 10 different regions and several states in each region. How can I accomplish this?

Thanks for the help.

A
Ray PaseurCommented:
Just to clarify, there is a database table that contains columns named region and state.  So something like this would get all of the data you need:

SELECT region, state FROM mytable ORDER BY region, state

And after that you want to iterate over the results set and produce HTML with the regions  in the header tags and the states in some kind of table?
aej1973Author Commented:
Hi Ray, my table has more columns than just those two. I would like to filter my results by just those two columns (state, region); first by region, then states specific to that region.

 My steps are as follows ( I am using codeigniter with active records )

model
function getAllRegions(){

    $where = "branch_id > 100 group by region" ;
    $this->db->where($where);
      $query = $this->db->get('table_branch');
      return  $query->result();
}

Controller
$data['regions'] = $this->modelviewdata->getAllRegions();
$this->load->view('Homepager',$data);

View
<?php foreach ($regions as $region): ?>
<h1>The Region is: <?php echo $region->region; ?>
<td>...not sure how to echo the states that fall under this region <td>

Thanks for the help.

A
Ray PaseurCommented:
Sorry, I don't know the CodeIgniter part of this, but maybe we can make progress without that.  Can you please go into the View script and use var_dump() to print out the contents of $regions?  When we can see what's in the data it will be helpful.
aej1973Author Commented:
The out put for the following code :

$result = array();
foreach($regions as $reg) {
  echo 'The region is'. $reg->region;
  foreach ($regions as $key => $value)
   {
       $result[] = $value->branch;    
     }
  }

 echo '<pre>';
    print_r($result);
echo '</pre>';

is as follows:

The region isCENTRALThe region isGULFThe region isMID-ATLANTICThe region isMIDSOUTHThe region isMIDWESTThe region isNEW ENGLANDThe region isNJ METROThe region isNORTHERN PLAINSThe region isNY METRO

Array
(
    [0] => CHICAGO                
    [1] => NEW ORLEANS            
    [2] => PHIL/PA/NJ/DE          
    [3] => ATLANTA                
    [4] => GRAND RAPIDS            
    [5] => HARTFORD/S CONN        
    [6] => NEW JERSEY              
    [7] => EASTERN IOWA            
    [8] => NEW JERSEY              
    [9] => BUFFALO                
    [10] => PHOENIX                
    [11] => SACRAMENTO              
    [12] => SAN FRANCISCO BAY      
    [13] => SEA/TAC                
    [14] => SAN DIEGO              
    [15] => KNOXVILLE              
    [16] => MIAMI/FTL/WPALM        
    [17] => ORL/MEL/DAYTONA        
    [18] => STUDIO STORE MKT        
    [19] => DALLAS/FT WORTH        
    [20] => CHICAGO            
}

Thanks,
A
Ray PaseurCommented:
Let me try this again...
Can you please go into the View script and use var_dump() to print out the contents of $regions?
aej1973Author Commented:
here it is;

Array
(
    [0] => stdClass Object
        (
            [branch_id] => 103
            [company] =>
            [address] =>
            [phone] =>
            [fax] =>
            [branch] => CHICAGO                
            [branch_code] => 75
            [price_percent] => 0.00
            [pricebook_id] =>
            [hermitage_region] => 0.00
            [permit_percent] => 0.00
            [permit_max] => 0.00
            [lfwp_amt] => 0.00
            [material_order_cc] =>
            [install_order_cc] =>
            [category] => CORE
            [region] => CENTRAL
            [vendor] =>
            [territory_code] => 0
            [status] => 0
            [delete_flag] => 0
            [last_modified_date] => 2015-06-03 20:51:43
        )

    [1] => stdClass Object
        (
            [branch_id] => 110
            [company] =>
            [address] =>
            [phone] =>
            [fax] =>
            [branch] => NEW ORLEANS            
            [branch_code] => 5
            [price_percent] => 0.00
            [pricebook_id] =>
            [hermitage_region] => 0.00
            [permit_percent] => 0.00
            [permit_max] => 0.00
            [lfwp_amt] => 0.00
            [material_order_cc] =>
            [install_order_cc] =>
            [category] => CORE
            [region] => GULF
            [vendor] =>
            [territory_code] => 0
            [status] => 0
            [delete_flag] => 0
            [last_modified_date] => 2015-06-03 20:51:43
        )

    [2] => stdClass Object
        (
            [branch_id] => 122
            [company] =>
            [address] =>
            [phone] =>
            [fax] =>
            [branch] => PHIL/PA/NJ/DE          
            [branch_code] => 34
            [price_percent] => 0.00
            [pricebook_id] =>
            [hermitage_region] => 0.00
            [permit_percent] => 0.00
            [permit_max] => 0.00
            [lfwp_amt] => 0.00
            [material_order_cc] =>
            [install_order_cc] =>
            [category] => CORE
            [region] => MID-ATLANTIC
            [vendor] =>
            [territory_code] => 0
            [status] => 0
            [delete_flag] => 0
            [last_modified_date] => 2015-06-03 20:51:44
        )

    [3] => stdClass Object
        (
            [branch_id] => 125
            [company] =>
            [address] =>
            [phone] =>
            [fax] =>
            [branch] => ATLANTA                
            [branch_code] => 1
            [price_percent] => 0.00
            [pricebook_id] =>
            [hermitage_region] => 0.00
            [permit_percent] => 0.00
            [permit_max] => 0.00
            [lfwp_amt] => 0.00
            [material_order_cc] =>
            [install_order_cc] =>
            [category] => CORE
            [region] => MIDSOUTH
            [vendor] =>
            [territory_code] => 0
            [status] => 0
            [delete_flag] => 0
            [last_modified_date] => 2015-06-03 20:51:44
        )
)
Ray PaseurCommented:
Is this the var_dump() output?  It does not tell us the visibility of the properties!  There is a reason why we would want to know the visibility - it affects how we would program the solution.  Fortunately it looks like we are working with object instances of StdClass.

At least I can give you the general strategy, as follows:

1. Set $old_region to '?'
2. Iterate over the results set
3. Compare $reg->region to $old_region
3. If these are different:
3a. Set $old_region = $reg->region
3b. Start a new output group (maybe H1 tags?) for $reg->region
4. Add $reg->branch to the output stream
5. Repeat until the results set is exhausted.
aej1973Author Commented:
Ray, this is the output of var_dump;

array(20) {
  [0]=>
  object(stdClass)#20 (22) {
    ["branch_id"]=>
    string(3) "103"
    ["company"]=>
    NULL
    ["address"]=>
    string(0) ""
    ["phone"]=>
    string(0) ""
    ["fax"]=>
    string(0) ""
    ["branch"]=>
    string(24) "CHICAGO                 "
    ["branch_code"]=>
    string(2) "75"
    ["price_percent"]=>
    string(4) "0.00"
    ["pricebook_id"]=>
    string(0) ""
    ["hermitage_region"]=>
    string(4) "0.00"
    ["permit_percent"]=>
    string(4) "0.00"
    ["permit_max"]=>
    string(4) "0.00"
    ["lfwp_amt"]=>
    string(4) "0.00"
    ["material_order_cc"]=>
    string(0) ""
    ["install_order_cc"]=>
    string(0) ""
    ["category"]=>
    string(4) "CORE"
    ["region"]=>
    string(7) "CENTRAL"
    ["vendor"]=>
    string(0) ""
    ["territory_code"]=>
    string(1) "0"
    ["status"]=>
    string(1) "0"
    ["delete_flag"]=>
    string(1) "0"
    ["last_modified_date"]=>
    string(19) "2015-06-03 20:51:43"
  }
  [1]=>
  object(stdClass)#21 (22) {
    ["branch_id"]=>
    string(3) "110"
    ["company"]=>
    NULL
    ["address"]=>
    string(0) ""
    ["phone"]=>
    string(0) ""
    ["fax"]=>
    string(0) ""
    ["branch"]=>
    string(24) "NEW ORLEANS             "
    ["branch_code"]=>
    string(1) "5"
    ["price_percent"]=>
    string(4) "0.00"
    ["pricebook_id"]=>
    string(0) ""
    ["hermitage_region"]=>
    string(4) "0.00"
    ["permit_percent"]=>
    string(4) "0.00"
    ["permit_max"]=>
    string(4) "0.00"
    ["lfwp_amt"]=>
    string(4) "0.00"
    ["material_order_cc"]=>
    string(0) ""
    ["install_order_cc"]=>
    string(0) ""
    ["category"]=>
    string(4) "CORE"
    ["region"]=>
    string(4) "GULF"
    ["vendor"]=>
    string(0) ""
    ["territory_code"]=>
    string(1) "0"
    ["status"]=>
    string(1) "0"
    ["delete_flag"]=>
    string(1) "0"
    ["last_modified_date"]=>
    string(19) "2015-06-03 20:51:43"
 
}}
Ray PaseurCommented:
Looks OK.  Strategy remains the same.  Suggest you trim() the branch property before using it in HTML.
aej1973Author Commented:
Ray, what do you mean by "Set $old_region to '?'" ?

Thanks,
A
Ray PaseurCommented:
I mean literally this statement:
$old_region = '?';

Open in new window

The point is to initialize the $old_region value to a value that cannot be in the database, and will therefore trigger the unequal comparison in step 3.  This needs to happen the first time through and every time the region changes.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.