Count distinct cells in one column with zend_db_select PHP

Question

I'm trying to count the number of ocurrences of specific cells in ONE column(test) and then be able to echo them as arrays in one query. exmp.

**user_id    test**
     1       echo
     1       angio
     1       holter
     1       angio
     1       echo
     1       angio

I want to be able to echo the count of each test as an array like below;

$result['echo'] = 2
$result['holter'] = 1
$result['angio'] = 3

I'm able to achieve the number of each test by multiple queries (one query per each test), which seems very inefficient, and getting the results as array seems to be the solution. using zend_bd_select and would greatly appreciate any help.

Answer 1

This uses a simple Zend_Db_Select query and a loop to process the results into an array like you have shown.

$groups = array();  // same as $result in your question

$select = $dbTable->select()
                  ->from($dbTable, array('user_id', 'test'))
                  ->where('user_id = ?', $user_id);

$result = $select->query();

while (($row = $result->fetch()) !== false) {
    $test = $row['test'];
    if (!isset($groups[$test])) {
        $groups[$test] = 1;
    } else {
        $groups[$test]++;
    }
}

At the end, $groups should be indexed by the test column values with a count of how many times it occurred. I do not believe there is a single query you can run to achieve this result, at least not without stored procedures or running many subqueries.