将SQL PHP数组添加到现有数组中

Question

I have two database tables. One contains Marketplaces and the other contains the Locations of the Marketplaces. Marketplaces can have multiple locations the two tables are joined by the MarketplaceID. I need to query the marketplaces turn it into an array and during the foreach or the while, add the locations as an array to the marketplaces. This is all part of two json responses. However my goal is to combine the data together and have one json response where the locations are within the marketplaces json data as "Locations". Ultimately an array where Marketplaces is the parent and Locations are the children.

Code 1 (Table1 Query):

$sql = "SELECT * FROM Marketplaces WHERE Status = 1";
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result)){   
  foreach($row as $key => $value){
      $arr[$key] = $value;
  }
  $main_arr[] = $arr;
}
return $main_arr;

Result:

[{"MarketplaceID":"1","AccountID":"0","BusinessName":"Mike's Pub","BusinessType":"1","Status":"1","CreationDate":"0000-00-00 00:00:00"}]

Code 2 (Table2 Query):

$sql = "SELECT * FROM Locations WHERE MarketplaceID = 1";
$results = mysql_query($sql);

while($row = mysql_fetch_assoc($results)){
  foreach($row as $key => $value){
    $arr[$key]=$value;
  }
  $main_arr[] = $arr;
}

Result:

[{"LocationID":"1","MarketplaceID":"1","Address1":"5055 Business Center Drive","Address2":"Suite 100","City":"San Francisco","CA":"1","Zip":"90210","Phone1":"(555) 555-555","Phone2":"[phone]","EmailAddress":"[e-mail]","StoreNumber":"200"},{"LocationID":"2","MarketplaceID":"1","Address1":"112 Street Court","Address2":"","City":"Los Angeles","StateID":"2","Zip":"90210","Phone1":"[phone]","Phone2":"","EmailAddress":"[e-mail]","StoreNumber":"300"}]

Answer 1

First, drop this

  foreach($row as $key => $value){
      $arr[$key] = $value;
  }

It does nothing except the same as $arr = $row; Actually it does something else - produces a warning as $arr is undefined.

EDIT Updated answer now that the initial query has been changed from WHERE MarketplaceID=1 to WHERE Status = 1"

$main_arr=array();
$sql = "SELECT * FROM Marketplaces WHERE Status = 1";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)){   
  $row['locations']=array();
  $sql2 = "SELECT * FROM Locations WHERE MarketplaceID = ".$row['MarketplaceID'];
  $results2 = mysql_query($sql2);
  while($row2 = mysql_fetch_assoc($results2)){
      $row['locations'][]=$row2;
  }
  $main_arr[] = $row; 
}

// print_r($main_arr); and view page source to read, rather than a JSON string

Actually I would do this slightly differently so that my array doesn't muddle locations inside of Marketplace but still keeps them together, like this below

while($row = mysql_fetch_assoc($result)){   
  $thisrow=array('marketplace'=>$row, 'locations'=>array());
  $sql2 = "SELECT * FROM Locations WHERE MarketplaceID = ".$row['MarketplaceID'];
  $results2 = mysql_query($sql2);
  while($row2 = mysql_fetch_assoc($results2)){
      $thisrow['locations'][]=$row2;
  }
  $main_arr[] = $thisrow; 
}

so the object would be

{ 
    "marketplace": {"MarketplaceID": 1, "BusinessName": "Fred Bloggs", ... }
    "locations": [
        {"LocationId": 1, "Address1": "Some address"},
        {"LocationId": 2, "Address1": "Some address"}
    ]
}