PHP mysql select to array and check if value exist

Question

I have this MySQL table:

tab

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

Is there any table or array with predefined list of Platforms ? — Rikesh
– Rikesh, Commented Jan 27, 2014 at 13:57
@Rikesh I have array in php function collecting data. Array is : [windows, linux, mac, unknown] — Ing. Michal Hudak
– Ing. Michal Hudak, Commented Jan 27, 2014 at 13:58

gpopoteur · Accepted Answer · 2014-01-27 15:24:16Z

4

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.

edited Jan 27, 2014 at 15:24

answered Jan 27, 2014 at 14:02

gpopoteur

1,51910 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ing. Michal Hudak Over a year ago

Your first array $platforms should be named $data, so function will replace values.

mr. Pavlikov · Accepted Answer · 2014-01-27 14:04:07Z

2

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

answered Jan 27, 2014 at 14:04

mr. Pavlikov

1,0126 silver badges7 bronze badges

Comments

younis · Accepted Answer · 2014-01-27 14:05:46Z

2

You must have another table which has all the platforms listed there. For example we call it platforms which has a column platform_name with all the unique names of platforms (including mac), then the query would be:

SELECT platforms.`platform_name`, COUNT(stats.`platform`) FROM `platforms`
LEFT JOIN `stats` ON  platforms.`platform_name` = stats.`platform`
GROUP BY platforms.`platform_name`

answered Jan 27, 2014 at 14:05

younis

3571 silver badge7 bronze badges

Collectives™ on Stack Overflow

PHP mysql select to array and check if value exist

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related