Counting row occurrences and printing them

Question

A MySQL table named Tags contains 2 columns: tags and video_id with over 100k rows. None of them is primary key which means that values in the tags column might occur few times with different video_id, for example:

column names:  |tags|video_id|
values:         tag1 video1
                tag1 video2
                tag2 video4
                tag2 video5
                tag2 video6

How can I count all the occurrences of each tag and print it? Something like:

tag1 (2 Videos)

tag2 (3 Videos)

My website is based on CakePHP-3, the way I get the rows from the table is like that:

$query = $this->Tags->find('all');
$tags = $this->paginate($query);

The variable tags now has the results and that's how I print them:

foreach ($tags as $tag):
        echo $tag->tags."<br>";
endforeach;

Show source
| cakephp   | php   | cakephp-3.0   | mysql   | query-builder   2017-01-05 17:01 2 Answers

Answers to Counting row occurrences and printing them ( 2 )

  1. 2017-01-05 17:01

    You can do so by leveraging basic SQL logic, that is for example count video_id (or *, which might give you better performance under certain circumstances) and group by tags.

    $query = $this->Tags->find();
    $query = $query
        ->select([
            'tags',
            'count' => $query->func()->count('video_id')
        ])
        ->group('tags');
    
    $tags = $this->paginate($query);
    

    This will create a query similar to:

    SELECT tags, COUNT(video_id) as count
    FROM tags
    GROUP BY tags
    

    The resulting entities will hold the count property that you can access like any other property. Here's a small example, additionally using plural aware translation functions (surely not required, but might be useful):

    foreach ($tags as $tag):
        echo $tag->tags . ' (' .
            __n('{0} Video', '{0} Videos', $tag->count, $tag->count) .
        ')<br>';
    endforeach;
    

    See also

  2. 2017-01-05 18:01

    You can use cakephp count() function and Group options

    $query = $this->Tags->find('all');
    $tags = $query->select([ 
                  'tags',
                  'count' => $query->func()->count('*')
                ])
         ->group('tags');
    

    you can get result by

    foreach ($tags as $tag):
            echo $tag->tags."(".$tag->count." Videos)";
    endforeach;
    

Leave a reply to - Counting row occurrences and printing them

◀ Go back