如何从DB获取数组

I've got a question.

I've got a table table1 like this:

ID | email | first_name | last_name
1  | bla   | bladibla   | bladibladibla

And with that, I got another table table2:

ID | table1_id | name | value
1  | 1         | foo  | bar
2  | 1         | foo1 | bar1
3  | 1         | foo2 | bar2

My question: How do I get the results in php like this?

$array = [
    id => 1,
    email => bla,
    first_name => bladibla,
    last_name => bladibladibla,
    data => [
        [
            name => foo,
            value => bar
        ],
        [
            name => foo1,
            value => bar1
        ],
        [
            name => foo2,
            value => bar2
        ],
    ]
]

My solution that works for now, but does not seem the best way to handle it.

I have this now, but it does not seem to be the right way to do it, because in the first foreach loop, it's shooting a query to the db for every row in table1.

$query = "SELECT * FROM `table1`";
$results = $wpdb->get_results($query);

foreach($results as $result) {
    $otherQuery = "SELECT * FROM `table2` WHERE `table1_id` = " . $result['id'];
    $table2Results = $wpdb->get_results($otherQuery);

    foreach($table2Results as $table2Result) {
        // save all data in a new array just like I want it.
    }
}

$query = "Select 
               t1.ID,t1.email,t1.first_name,t2.last_name,t2.name,t2.value
          From 
              table1 t1 
          Inner Join 
              table2 t2 
          On t1.ID=t2.table1_id";

$results = $wpdb->get_results($query);
$arr1=[];
$arr2=[];
foreach($results as $result) {       
    $arr1[]=['id'=>$result['ID'],'email'=>$result['email'],
             'first_name'=>$result['first_name'],
             'last_name'=>$result['last_name'],'data'=>[]];        
    $arr2[]=['name' => $result['name'],'value' => $result['value']];
}

$arr1=array_unique($arr1);
$arr1['data'][]=$arr2;

Try this :

$query = "SELECT * FROM `table1`";
$results = $wpdb->get_results($query);
$arr = []; // $arr = array();
foreach($results as $result) {
    $temp = $result;
    $otherQuery = "SELECT * FROM `table2` WHERE `table1_id` = " . $result['id'];
    $table2Results = $wpdb->get_results($otherQuery);

    foreach($table2Results as $table2Result) {
        // save all data in a new array just like I want it.
        $temp['data'][] = $table2Result
    }

    $arr[] = $temp;
}

print_($arr);

$data = array();
$i = 0;
foreach($results as $result) 
{
        $data[$i] = $result;

        $otherQuery = "SELECT * FROM `table2` WHERE `table1_id` = " . $result['id'];
        $table2Results = $wpdb->get_results($otherQuery);

        $j = 0;
        $data[$i]['data'] = array();
        foreach($table2Results as $table2Result)
        {
                $data[$i]['data'][$j] = $table2Result;
                $j++;
        }
        $i++;
}
print_r($data);

You should be getting duplicates in the JOIN, since your example array shows that each table1 record is related to more than one table2 record. So doing multiple queries isn't going to avoid that. What you want, I think is something like:

$query = "SELECT * FROM table1 LEFT JOIN table2 WHERE table1.id = table2.table1_id";
$results = $wpdb->get_results($query);

foreach($results as $result) {

    $table1_id = $result["table1.id"];

    $entries[$table1_id]['id'] = $table1_id;
    $entries[$table1_id]['email'] = $result["table1.email"];
    $entries[$table1_id]['first_name'] = $result["table1.first_name"];
    $entries[$table1_id]['last_name'] = $result["table1.last_name"];

    $entries[$table1_id]['data'][] = array($result["table2.name"], $result["table2.value"]);

}

print_r($entries) // the full array of table1 entries with the sub-array of table2 entries per table1 entry.

By keying each array entry to the table1.id, you ensure that even when they are repeated, the table2 entries get added as new entries to the data array, while not creating an new array entry for the main "outer" table1 record.

If you don't want the table1.id key on the array itself (and have a simple 0-based indexed array, you could re-index after you are done in the loop like:

$entries = array_values($entries);