php - MYSQL/JSON 输出计数和百分比

Question

我现在拥有的..

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

我想做的是把它放出来[{"status":"value","kpi":"value","count":"12""percentage":"%32.42"}

下面是我的 php 代码。

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

Answer 1

如果您的数据库中有一列包含百分比，那么您只需选择:

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

如果您想计算一些东西，您可以选择在查询中或在 PHP 中进行计算。例如，在 PHP 中，您可以这样做:

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

如评论中所述

如果可以，你应该stop using mysql_* functions . These extensions已在 PHP 7 中删除。了解 prepared PDO 的声明和 MySQLi并考虑使用 PDO，it's really not hard .