php - 从 Db 生成 JSON bool 值

Question

我需要使用 this format 生成一个 JSON 字符串:

{"content":{"brands":1},"brands":[{"id":"1","name":"brand 1","description":"description","icon":"icon","url":"example.com","categories":{"1":"true","2":"true","3":"false","4":"false","5":"false","6":"false"}},{"id":"2","name":"brand2","description":"description","icon":"icon","url":"example.com","categories":{"1":"true","2":"true","3":"false","4":"false","5":"false","6":"false"}}]}

来自这个表:

品牌:

| id | name   | description | icon | url |
|----|--------|-------------|------|-----|
|  1 | name 1 | description | icon | url |
|  2 | name 2 | description | icon | url |
|  3 | name 3 | description | icon | url |
|  4 | name 4 | description | icon | url |
|  5 | name 5 | description | icon | url |
|  6 | name 6 | description | icon | url |

类别:

| id | name   | description | icon | url |
|----|--------|-------------|------|-----|
|  1 | name 1 | description | icon | url |
|  2 | name 2 | description | icon | url |
|  3 | name 3 | description | icon | url |
|  4 | name 4 | description | icon | url |
|  5 | name 5 | description | icon | url |
|  6 | name 6 | description | icon | url |

对象:

| id | id_brand | id_category |name   | description | icon | url |
|----|----------|-------------|-------|-------------|------|-----|
|  1 |     1    |       1     |name 1 | description | icon | url |
|  2 |     1    |       2     |name 2 | description | icon | url |
|  3 |     2    |       1     |name 3 | description | icon | url |
|  4 |     2    |       2     |name 4 | description | icon | url |

到目前为止，这是我的相关代码

public function actionBrand($id = null) {
    if (empty($id)) {
        // Obtiene datos de la base
        $sql = "SELECT DISTINCT objects.id_brand AS id, brands.name AS name, brands.description AS description, brands.icon AS icon, brands.url AS url, objects.id_category, categories.name AS category " .
                "FROM objects " .
                "LEFT JOIN brands ON objects.id_brand = brands.id " .
                "LEFT JOIN categories ON objects.id_category = categories.id " .
                "ORDER BY objects.id_brand, objects.id_category ";
    } else {
        // Obtiene datos de la base
        $sql = "SELECT DISTINCT objects.id_brand AS id, brands.name AS name, brands.description AS description, brands.icon AS icon, brands.url AS url, objects.id_category, categories.name AS category " .
                "FROM objects " .
                "LEFT JOIN brands ON objects.id_brand = brands.id " .
                "LEFT JOIN categories ON objects.id_category = categories.id " .
                "WHERE brands.id = " . (int) $id . " " .
                "ORDER BY objects.id_brand, objects.id_category ";
    }

    $data = Yii::$app->db->createCommand($sql)
            ->queryAll();

    // Obtiene categorias
    $categories = Yii::$app->db->createCommand('SELECT id FROM categories ORDER BY id')
            ->queryAll();

    if (!empty($data)) {
        // Construye primer registro
        $brands[0]['id'] = $data[0]['id'];
        $brands[0]['name'] = $data[0]['name'];
        $brands[0]['description'] = $data[0]['description'];
        $brands[0]['icon'] = $data[0]['icon'];
        $brands[0]['url'] = $data[0]['url'];

        $total = count($data);
        for ($i = 1, $j = 0; $i < $total; $i++) {
            if ($brands[$j]['id'] == $data[$i]['id']) {
                continue;
            } else {
                $j++;

                $brands[$j]['id'] = $data[$i]['id'];
                $brands[$j]['name'] = $data[$i]['name'];
                $brands[$j]['description'] = $data[$i]['description'];
                $brands[$j]['icon'] = $data[$i]['icon'];
                $brands[$j]['url'] = $data[$i]['url'];
            }
        }
    } else {
        $brands = array();
    }

    // Construye y envia JSON
    $json['content']['brands'] = count($brands);
    $json['brands'] = $brands;
    echo json_encode($json);
}

它生成 first part我需要的 JSON，但我停留在类别部分，我需要从基础中选择数据并将其转换为 id:每个品牌的 (true)(false)

{"content":{"brands":1},"brands":[{"id":"1","name":"brand 1","description":"description","icon":"icon","url":"example.com"},{"id":"2","name":"brand2","description":"description","icon":"icon","url":"example.com"}]}

你能帮帮我吗？

问候

Answer 1

完成品牌构建后，在类别上循环，搜索现有 ID 匹配的品牌。如果匹配，则为类别调整真假值。然后，当类别完成后，将它们附加到最后一个循环中的每个品牌。

$cleancats = [];
foreach ($categories as $cat) {
    $result = false;
    foreach($brands as $brand) {
        if ($cat['id'] == $brand['id']) {
            $result = true; break;
        }
    }
    $cleancats[ $cat['id'] ] = $result;
}
array_walk ($brands,function(&$brand) use ($cleancats) {
    $brand['categories'] = $cleancats;
});

(注意把它放在你的 for ($i = 1, $j = 0; $i < $total; $i++) 循环结束之后)

如您所愿，这应该会获得品牌中每个品牌的类别。

如果您需要类别是字面意义上的“真”和“假”，请调整上面的这一行:

    $cleancats[ $cat['id'] ] = ($result?'true':'false');