phpmysqlzend-dbzend-db-select

How can I improve the following mysql selects?


I am currently refactoring a legacy application and converting piece by piece over to zend framework 1.12.

I am scratching my head as to how to convert this over to zend db, is there a way that this can be done in one query?

Right now I see that it fetches a list of folders first and then runs an additional query per folder... Running this as one query will improve performance, right?

$folders_query = DB::Query("select * from contacts_folders order by sort_order, name");
while($folders = DB::FetchArray($folders_query)){
    $counts_total = DB::QueryOne("SELECT count(cm.messages_id) AS total
    FROM contacts_basics cb, contacts_messages cm
    WHERE cb.contacts_id = cm.contacts_id
    AND cm.folders_id =  '" . $folders['folders_id'] . "'
    AND cm.status = '1'
    AND cm.mark =  '0'");


    if ($counts_total >0){ 
        $folders_name = '<strong>' . $folders['name'] . ' (' . $counts_total . ')</strong>'; 
    } else { 
        $folders_name = $folders['name']; 
    }

    echo '<li><a href="messages.php?fID=' . $folders['folders_id'] . '">';

    echo $folders_name;

    echo '</a></li>';
}

Solution

  • Yes, you can do both in the same query

    SELECT cf.*, count(cm.messages_id) AS total
    FROM contacts_folders cf left outer join
         contacts_messages cm
         on cf.id = cm.folders_id and
            cm.status = '1' AND cm.mark =  '0' left outer join
         contacts_basics cb
         on cb.contacts_id = cm.contacts_id
    group by cf.folders_id
    order by cf.sort_order, cf.name;
    

    This uses a left outer join to be sure that you get all folders, even if there are no messages (which is how the original code works). Because of the left outer join, the conditions need to be moved into on clauses.

    It also fetches all the information from the folders as well as the total. If there are no messages, then it should return 0 for that folder.