CODEWITHSUNDEEP
phpmysql
Ing. Michal Hudak
Ing. Michal Hudak

Reputation: 5612

PHP mysql select to array and check if value exist

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:

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

Upvotes: 2

Views: 2412

Answers (3)

gpopoteur
gpopoteur

Reputation: 1519

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.

Upvotes: 4

younis
younis

Reputation: 357

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`

Upvotes: 2

mr. Pavlikov
mr. Pavlikov

Reputation: 1012

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

Upvotes: 2

Related Questions