CODEWITHSUNDEEP
phpmysqljsonyii2
Chico3001
Chico3001

Reputation: 1963

Generate JSON booleans from Db

I need to generate a JSON String using this format:

{"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"}}]}

From this tables:

brands:

| 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 |

categories:

| 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 |

objects:

| 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 |

this is my relevant code so far

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);
}

It generates the first part of the JSON that i need, but im stuck at the categories part i need to select the data from the base and convert it to id : (true)(false) on each brand

{"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"}]}

Can you help me?

Regards

Upvotes: 2

Views: 51

Answers (1)

IncredibleHat
IncredibleHat

Reputation: 4104

After you are done building the brands, loop on the categories, searching brands for the existing id match. If it matches, then adjust a true false value for the categories. Then when the categories are completed, append them to every brand in a final loop.

$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;
});

(note put this after your for ($i = 1, $j = 0; $i < $total; $i++) loop ends)

That should get the categories to every brand in brands, as you would like.

If you need the categories to be a LITERAL "true" and "false" then adjust this one line above:

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

Upvotes: 3

Related Questions