Display the 10 most used tags

I am developing an API using Codeigniter and in this API the users can tag themself. I need to display the 10 most popular tags (most frequestly used tags). How can I do that?

Update

The tag table contains:

id, user_id, tag, created_at, updated开发者_如何学运维_at

---

Jonathan, depending on your setup the solution will be different, I'm going to suggest something a bit different than what your table structure looks like, you can use it if you want to.

.--------------.    .-------------.
|   userTags   |    |     tags    |
.--------------.    .-------------.
|  id          |    | id          |
|  user_id     |    | tagName     |
|  tag_id      |    .-------------.
.--------------.

To add a tag to your database: $data = array('tagName' => 'Mr. Niceguy'); $this->db->insert('tags' => $data);

To add a tag to your user, you could use the following in a model

$data = array('user_id' => $uId, 'tag_id' => 1);
$this->db->insert('userTags', $data);

To recieve the most common used tags:

$this->db->select('tag_id');
$this->db->order('tag_id DESC');
$this->db->limit(10);
$this->db->join('tags', 'userTags.tag_id = tags.id');
$this->db->get('userTags');

Edit:

If you have to use your setup, do this instead:

$this->db->order_by('count(tag)', 'DESC');
$this->db->group_by('tag', 'DESC');
$tags = $this->db->get('userTags');

foreach($tags->result() as $tag) {
    var_dump($tag->tag);
}

---

You'll need to use a combination of order_by() and group_by(). Group them by the tag name, and then order them by the count. I'm not sure if you can order by count in ActiveRecord though, so you might need to use raw SQL.