[eluser]mvdg27[/eluser]
Hi,
I have a question about a website I'm currently building. It's not directly CI related, but since the support here is great (thanks for that!) .. I want to give it a go.
In short, I have 2 tables: one with a list of links (id#name#url#category) and one in which I store all the clicks on the links (link_id#user_id#datetime).
As you can see I store the user_id for every click. Now I'd like to print out a list of links based on popularity. I figured out how to print the overall popularity (disregarding the user_id). But I'd like to go one step further: I would like to first sort by popularity for the current user and then print the rest of the links based on the overall clicks.
This would mean that if a user hasn't yet clicked on a link, the overall order will be displayed. But at the moment a user has clicked 1 or more links, these links will always be displayed first.
I hope you guys understand my train of thought here.
This is the code I have so far:
Code:
$this->db->select('links.id, links.name, count(clicks.link_id) AS clicked');
$this->db->where('links.category_id', $cat['id']);
$this->db->join('clicks', 'links.id = clicks.link_id', 'left');
$this->db->group_by('links.id');
$this->db->order_by('clicked', 'desc');
$query = $this->db->get('links');
Looking forward to your suggestions!
Cheers, Michiel
