CodeIgniter fetch data from 2 tables and sum

Question

I have two db tables clients and bookings

Clients table contains:

[client_ref] [client_name]
 2378       | My Client
 8746       | Other Client

Bookings table contains

[booking_client] [booking_num]
 2378           | 1
 2378           | 5
 8746           | 3

Currently in model i have this query

function clients()
{
    $query = $this->db
        ->select('*')
        ->order_by('client_name', 'ASC')
        ->get('clients');
    return $query->result();
}

Then controller to set $data value

$data['clients'] = $this->home_model->clients();
$this->load->view('pages/clients', $data);

And in view

foreach ($clients as $client)
{
echo $client->client_name . " - " .$client->client_ref . "<br />";
}

It currently displays list of clients (name - ref).

I want to make model to query those 2 tables, SUM booking_num and order record by SUM descending.

parth · Accepted Answer · 2016-09-14 04:33:18Z

1

You have to use the group by and sum SQL functions, as below

$this->db->select('clients.client_ref, clients.client_name, sum(bookings.booking_num) as sum_booking_num');
$this->db->from('clients');
$this->db->join('bookings', 'bookings.booking_client = clients.client_ref', 'right');
$this->db->group_by('clients.client_ref');
$this->db->order_by('sum_booking_num', 'desc');
$clients = $this->db->get()->result_object();

Click here for demo.

edited Sep 14, 2016 at 4:33

answered Sep 14, 2016 at 4:01

parth

1,9203 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CodeIgniter fetch data from 2 tables and sum

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related