MYSQL / JSON Output Count & Percentages

Question

What I currently have..

[{"status":"C - Net & Phone","kpi":"Lead","count":"12"}

What I am trying to do is have it put out [{"status":"value","kpi":"value","count":"12""percentage":"%32.42"}

Here is my php code below.

<?php 
        $Connection = mysql_connect('localhost', '', '');
            if (!$Connection) {
                die('ACCESS DENIED' . mysql_error());
        }

        $Database = mysql_select_db('main', $Connection);
            if (!$Database) {
                die ('DIED' . mysql_error());
        }

         $ $query = " SELECT pin_status,kpi_type,COUNT(*) FROM main_pins GROUP BY pin_status;  ";

            $result = mysql_query( $query );
                if ( !$result ) {
                    $ErrorMessage  = 'Invalid query: ' . mysql_error() . "\n";
                    $ErrorMessage .= 'Whole query: ' . $query;
                die( $ErrorMessage );
        }

        $JSON_output = array();
            while ( $row = mysql_fetch_assoc( $result ) )
        {

        $JSON_output[] = array('status'         => $row['pin_status'],
                                'kpi'           => $row['kpi_type'], 
                                'count'         => $row['COUNT(*)'],
                                 'percentage'     => $row[????], // what I would like added
                            );
        }

header( "Content-Type: application/json" );

    $JSON_output = json_encode($JSON_output);

echo $JSON_output . "";

mysql_close($Connection);
?>

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Sep 23 '15 at 19:55
Is percentage a column in your table? Or are you trying to calculate a percentage? You also have at least one typo involving a `$` — Jay Blanchard, Sep 23 '15 at 19:56
I am trying to calculate a percentage. Similar to what I did with the COUNT(*). — Levi, Sep 23 '15 at 19:59
yes I see the $ next to query. That must have been a typo after i put the question together. — Levi, Sep 23 '15 at 20:00
Without knowing what the table contains it would be hard to tell you exactly what to do here. You would have to have the total number of KPI type to divide by the count of this particular count to do the percentage, so doing it in the query is not really an option *and may* take two queries - one to get the total KPI and then the query you show here. — Jay Blanchard, Sep 23 '15 at 20:06

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

If you have a column in your database that contains the percentage then all you should do is select that:

$query = " SELECT `pin_status`, `kpi_type` ,COUNT(*) AS `current_count`, `percentage` FROM `main_pins` GROUP BY `pin_status`;  ";

If you want to calculate something, you have a choice of doing it in the query or in the PHP. For example, in PHP you might do this:

while($row = mysql_fetch_assoc( $result )) {
    $percentage = ($row[$x]/$y) * 100; // supply $x and $y
    $JSON_output[] = array('status'         => $row['pin_status'],
                       'kpi'           => $row['kpi_type'], 
                       'count'         => $row['current_count'],
                       'percentage'     => $percentage
    );
}

As mentioned in comments

If you can, you should stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really not hard.

edited May 23 '17 at 12:29

Community

1
1

answered Sep 23 '15 at 20:03

Jay Blanchard

34,243
16
77
119

I do not have the column percentage. Simply trying to create a column like I did for count, but display the percentage based off of kpi_type. – Levi Sep 23 '15 at 20:04
What do you mean supply $x and $y ?? – Levi Sep 24 '15 at 04:46
[{"status":"Sold","kpi":"Sale","count":null,"percentage":0}] there is several hundred status and kpi types. – Levi Sep 24 '15 at 05:01
Supply `$x` and `$y` means to provide the data you're using to calculate the percentage @Lzoesch. – Jay Blanchard Sep 24 '15 at 11:30
so $x would be kpi_type, and $y would be deleted ? – Levi Sep 24 '15 at 20:01
Without seeing your table I cannot tell you if that is correct. Can you edit your original post and add some of your current table information? – Jay Blanchard Sep 25 '15 at 11:50

score 1 · Accepted Answer · answered Oct 02 '15 at 08:33

update your query to look like this.

$query = "SELECT pin_status, kpi_type, Count(*) as count,((Count(*)  * 100.0)/ (select Count(*) FROM main_pins)) AS Percentage
                    FROM main_pins
                    GROUP BY pin_status, kpi_type
                    ORDER BY pin_status, kpi_type
                    DESC";

add to your JSON output.

$JSON_output[] = array('Status'         => $row['pin_status'],
                       'KPI'            => $row['kpi_type'],
                       'Percentage'         => $row['Percentage'],
                       'Count'          => $row['count'],
);}

MYSQL / JSON Output Count & Percentages

2 Answers2