用于组织数据库中的值的代码的效率

Question

I have to put my database's data in this format (values will differ, obviously), I think it's called an associative array (I'm horrible with terminology).

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

Here's what I developed:

    $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'];
    }
}

Output:

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

Answer 1

You can do all of this in a single query:

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

Then the outer loop goes away and the inner loop becomes:

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

It should perform noticeably better for moderately sized data sets. Do note, however, that by using the month function, you lose any benefit of indexes that you might otherwise be able to use.