Codeigniter displaying total added likes from mysql table

Question

I am using MVC in codeigniter and I have a MySql table that has pages made by users page_id and topics topic_number as shown in the image below (Table page). pages can have multiple topic numbers.

I also have another table called table page likes (shown in the image below also). This table also has page_id and topic_number but it has two more fields, which are, like and user_who_liked_topic_number.

My problem is that, I would like to display all the topic numbers that have or dont have likes. But if I only fetch topic numbers from the Table_page_likes, I will not get topic_numbers that dont have any likes recorded. which is why I am using both tables.

The first table is fetched from the database where all the Topic_number is printed out. Then each Topic_number is searched in the table_pages_like to find if it has any like records and display it next to the Page_id and topic_number.

This is my code so far, but I am not sure how to do the second step, which is to fetch each page_id with any number of likes from the table table_page_likes

controller_page.php

     function viewdata() {
    $data['pages'] = $this->model_page->get_pages();

    $this->load->view('view_page', $data);


}


function get_like($post_id, $topic_number) {

    $data['liked_pages'] = $this->model_page->get_likes_model($post_id, topic_number);

    $this->load->view('view_page', $data);


}

model_page.php

       function get_pages() {

    $query = $this->db->get_where('table_pages');
    return $query->result_array();

}

 function get_likes($page_id, $topic_number) {

        $query = $this->db->get_where('table_pages_likes', array('page_id' => $page_id , 'topic_number' => $topic_number));

}

view_page.php

              <?php foreach($pages as $value): ?>
      <tr>
        <td><?php echo $value['page_id']; ?></td>
        <td><?php echo $value['topic_number']; ?></td>

                <?php foreach($liked_pages as $value): ?>
                    <td><?php echo $value['page_id']; ?></td>
                    <td><?php echo $value['topic_number']; ?></td>
                    <td><?php echo $value['like']; ?></td>
                <?php endforeach; ?>
      </tr>
      <?php endforeach; ?>

Thanks in advance

Answer 1

You can simplify your model method to something like below instead of creating and calling two methods for displaying likes:

class model_page extends CI_Model {

    public function __construct()
    {
        $this->load->database();
    }

    public function get_page_likes() {
        $qry = $this->db->query("SELECT p.id, p.tid, IF(SUM(pl.like) > 0, SUM(pl.like), 0) AS likes FROM pages AS p LEFT JOIN page_likes pl ON p.id = pl.pid AND p.tid = pl.tid GROUP BY p.tid, p.id HAVING likes >= 0 ORDER BY p.id, p.tid");
        return $qry->result_array();
    }
}

Above query considers that you have the following tables similar to below (no indexes identified here yet):

CREATE TABLE `pages` (
  `id` int(11) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `page_likes` (
  `pid` int(11) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  `like` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And have the following sample data:

pages:
    id     tid  
------  --------
    11         1
    11         2
    11         3
    12         1
    12         2

page_likes
   pid     tid    like  
------  ------  --------
    11       1         1
    11       1         1
    11       1         1
    12       2         1

The above query will give you results like:

id     tid  likes   
------  ------  --------
    11       1  3       
    11       2  0       
    11       3  0       
    12       1  0       
    12       2  1               

Hope this helps.

EDIT: - edited method above.

On your controller:

public function index()
{
    $data['pages'] = $this->model_page->get_page_likes();
    $this->load->view('view_page', $data);
}   

On your view:

<table>
    <tr>
        <th>Page ID</th>
        <th>Topic ID</th>
        <th>Likes</th>
    </tr>
    <?php foreach($pages as $value): ?>
      <tr>
        <td><?php echo $value['id']; ?></td>
        <td><?php echo $value['tid']; ?></td>
        <td><?php if ($value['likes']) echo $value['likes']; else echo 'no likes to show'; ?></td>
      </tr>
    <?php endforeach; ?>
</table>