How to use CodeIgniter's query builder to return grouping columns with the number of related records from a joined table [duplicate]

Question

I want to count how many entries of game_slopes are in game_created_slopes with a specific value for id_sector. Basically I want to know how many slopes have been created for sector 1.

game_slopes is the table of generic items and game_created_slopes is the table of the actual items created by the members.

I tried to make a select query but not sure how to go for the count.

$this->db->select('game_slopes.id_slope, game_slopes.id_sector, game_created_slopes.id_slope, game_created_slopes.id_player');
$this->db->from('game_slopes, game_created_slopes');
$this->db->join('game_created_slopes as created_slopes_tbl', 'game_slopes.id_slope = created_slopes_tbl.id_slope');
$query = $this->db->get();


CREATE TABLE `game_slopes` (
  `id_slope` int(11) NOT NULL,
  `id_sector` int(11) NOT NULL,
  ....more stuff here....
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `game_created_slopes` (
  `id_created_slopes` int(11) NOT NULL,
  `id_player` int(11) NOT NULL,
  `id_slope` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If there is a better approach I'm up for it!

$query->num_rows() ; codeigniter.com/user_guide/database/… — cartalot
– cartalot, Commented Jun 8, 2016 at 21:38

Abdulla Nilam · Accepted Answer · 2016-06-09 00:03:26Z

2

You can archive this in three ways

In SQL way (Using Count Function)
In Codeigniter way (using num_rows() Function)
In PHP way (using count Function)

In SQL way

$this->db->select('COUNT(game_slopes.id_slope) as totalGames , game_slopes.id_slope, game_slopes.id_sector, game_created_slopes.id_slope, game_created_slopes.id_player');

In Codeigniter way

After $query = $this->db->get(); add this $totalGames = $query->num_rows();

$query = $this->db->get();
$totalGames = $query->num_rows();

In PHP way

$query = $this->db->get();
$result = $query->result_array();
$count = count($result);
if (empty($count)) {
    return false;
} else {
    return $result;
}

answered Jun 9, 2016 at 0:03

Abdulla Nilam

39.7k18 gold badges85 silver badges115 bronze badges

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

Collectives™ on Stack Overflow

How to use CodeIgniter's query builder to return grouping columns with the number of related records from a joined table [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related