Question: I have this MySQL table:

![tab](https://i.stack.imgur.com/UrYbo.png)

Where I want to get count of each platform (windows, linux, mac, unknown). 

*Notice that in table is no `mac` or `unknown` data, there for num will be `0`*

Conditions:

* order must be like in wanted output
* check if plaform is in table, if not add zero to output

**Question:** How to sort in query to have order like in wanted output, and add zero if no platform in table?

**Wanted output:**

```
array (
    '0' => array('name' => 'windows', 'num' => 3),
    '1' => array('name' => 'linux', 'num' => 3),
    '2' => array('name' => 'mac', 'num' => 0),
    '3' => array('name' => 'unknown', 'num' => 0)
);

```

**My try:**

**PHP:**

```
function get_platforms() {
    $query = "
            SELECT platform, COUNT(platform) AS num 
            FROM stats 
            GROUP BY platform 
            ORDER BY platform ASC
            ";
    $select = mysqli_query($this->c, $query);
    while ($row = mysqli_fetch_array($select)) {
        $data[] = array(
            'name' => $row['platform'],
            'num' => $row['num']
        );
    }
    return $data;
}

```

**Current outpup:**

```
array (
    '0' => array ('name' => 'linux', 'num' => 3)
    '1' => array ('name' => 'windows', 'num' => 3)
);

```

Answer: 
Because you don't have any mac or unknown data in your database, you will not get any result to this platform, not even 0. What you can do is to have a preconfigured array with the platforms you are expecting to get:

```
$data = array(
   'windows' => 0, 
   'linux' => 0,
   'mac' => 0,
   'unknown' => 0
)

```

Now in your `get_platforms` function do this:

```
function get_platforms() {
    $query = "
            SELECT platform, COUNT(platform) AS num 
            FROM stats 
            GROUP BY platform 
            ORDER BY platform ASC
            ";
    $select = mysqli_query($this->c, $query);
    while ($row = mysqli_fetch_array($select)) {
        $data[$row['platform']] = $row['num'];
    }
    return $data;
}

```

you will end up with this:

```
$data = array(
   'windows' => 3, 
   'linux' => 3,
   'mac' => 0,
   'unknown' => 0
)

```

And then you can reformat the data in the way that you want. For example you can do this:

```
$result = array();
foreach($data as $platform => $count){
    $result[] = array('name' => $platform, 'count' => $count);
}

```

And you will end up with the result in the same format you're asking.
First of all you should define an order of desired output, lets say you have array

```
$osList = array('linux', 'windows', 'mac', 'something');

```

Now fetch your mysql results to temporary assoc array:

```
$data = array();
$query = "SELECT platform, COUNT(platform) AS num"
    . " FROM stats"
    . " GROUP BY platform";
$result = mysqli_query($this->c, $query);
while ($row = mysqli_fetch_array($result)) {
    $data[$row['platform']] = (int) $row['num'];        
}

```

And here you can form output array:

```
$output = array();
foreach ($osList as $os) {
    $output[] = array(
        'name' => $os,
        'count' => isset($data[$os]) ? $data[$os] : 0
    );
}

```