php - foreach中的mysql查询

Question

我有一张 table :

<强>1。 ms_fields

+--+------+
|id|name  |
+--+------+
|1 |Color |
+--+------+
|2 |Gender|
+--+------+

<强>2。 ms_fields_values

+--+--------+------+--------+
|id|fieldsid|value |ordering|
+--+--------+------+--------+
|1 |1       |White |0       |
+--+--------+------+--------+
|2 |1       |Black |1       |
+--+--------+------+--------+
|3 |1       |Orange|2       |
+--+--------+------+--------+
|4 |1       |Green |3       |
+--+--------+------+--------+
|5 |1       |Blue  |4       |
+--+--------+------+--------+
|6 |2       |Male  |0       |
+--+--------+------+--------+
|7 |2       |Female|1       |
+--+--------+------+--------+

我的 PHP 代码:

function get_fields_and_values() {
   $sql_result = 'SELECT `id`,`name`,`ordering`
   FROM `ms_fields`
   WHERE 
   `id` IN (1,2)
   ORDER BY
   `ordering`
   ASC';

   $rows = $this->db1->query($sql_result)->resultset();

   $fields_list = array();
   if(count($rows)>0) {
      foreach ($rows as $row) {
         $row['values'] = self::load_values($row['id']);
         $fields_list[] = $row;
      } 
   }
}


 function load_values($fid) {
    $sql_result = 'SELECT 
    id, 
    fieldsid,
    value,
    ordering
    FROM 
    `ms_fields_values` 
    WHERE fieldsid = $fid';

    $rows = $this->db1->query($sql_result)->resultset();

    $values_list = array();

    if(count($rows)>0) {
     foreach ($rows as $row) {      
      $values_list[] = $row; 
     } 
    }
    return $values_list;
 }

如您所见，我的 load_values 函数在 foreach 语句中。这不好。是否可以更改第一个 sql 查询以使用 1 个请求获取字段和值？谢谢。

Answer 1

使用简单的JOIN

SELECT f.id, f.namr, fv.id, fv.value, fv.ordering 
FROM ms_fields f
LEFT JOIN ms_fields_values fv ON f.id = fv.fieldsid 
WHERE f.id IN (1, 2)
ORDER BY f.id, fv.ordering;