php - 从数据库中组织值的代码效率

Question

我必须以这种格式放置我的数据库的数据(值显然会有所不同)，我认为它被称为关联数组(我对术语很糟糕)。

$values=array(
            "Jan" => 110,
            "Feb" => 130,
            "Mar" => 215,
            "Apr" => 81,
            "May" => 310,
            "Jun" => 110,
            "Jul" => 190,
            "Aug" => 175,
            "Sep" => 390,
            "Oct" => 286,
            "Nov" => 150,
            "Dec" => 196
    );

这是我开发的:

    $sql = "SELECT MONTH(AddDate) AS Date, column_name FROM table ORDER BY AddDate ASC";
    $res = mysql_query($sql) or die(mysql_error());
    $prev_date = null;

$values=array();

while ( $row = mysql_fetch_assoc($res) ) {
    if ( $row['Date'] != $prev_date) {
        $month = $row['Date'];
        $sql = "SELECT count(MONTH(AddDate)) AS EntryAmount FROM `table` WHERE MONTH(AddDate)=$month ";
        $countResults = mysql_query($sql) or die(mysql_error());
        if( $entryAmount = mysql_fetch_array($countResults) ) {
            $values[$row['Date']] =  $entryAmount['EntryAmount'];
        }
        $prev_date = $row['Date'];
    }
}

输出:

Array ( [9] => 999 [10] => 986 [11] => 264 ) 

Answer 1

您可以在一个查询中完成所有这些:

select month(AddDate) as theMonth,
       count(*) as numberOfRows
from   `table`
group by theMonth
order by theMonth

然后外循环消失，内循环变成:

if( $row = mysql_fetch_array($results) ) 
{
    $values[$row['theMonth']] = $row['numberOfRows'];
}

对于中等规模的数据集，它的表现应该明显更好。但是请注意，通过使用 month 函数，您将失去索引的任何好处，否则您可能可以使用它。