CODEWITHSUNDEEP
phpmysqlarrayscodeigniteractiverecord
Eli
Eli

Reputation: 1276

Compare data in array and count the number of instances it occurs in the database using codeigniter

I have this array month $monthsNum = ['1','2','3','4','5','6','7','8','9','10','11','12'];, and I want this to be compared in my database column month_uploaded with values just ranging from 1-12. I want it to count the number of instances the $monthsNum array values occurs in column month_uploaded and stored it in array $count_uploads.Zero count is placed with zero value in array $count_uploads. How would I do that? Your help is much appreciated. Thank you. Below are pieces of my codes.

enter image description here

function count_uploads_perMonth(){

  $monthsNum = ['1','2','3','4','5','6','7','8','9','10','11','12'];

  $query = $this->db->select("*")
                    ->from($this->table_par)
                    ->where_in("month_uploaded",$monthsNum)
                    ->get();


      foreach( $query->result() as $row ){

        $count_uploads[] = count($row->month_uploaded);

      }

      var_dump($count_uploads);

}

Output: Wrong

array (size=5)
0 => int 1
1 => int 1
2 => int 1
3 => int 1
4 => int 1

Desired Output:

 array (size=12)
 0 => 0 or null
 1 => 0 or null
 2 => 0 or null
 3 => 0 or null
 4 => 0 or null
 5 => 1
 6 => 3 
 7 => 0 or null
 8 => 0 or null
 9 => 0 or null
10 => 0 or null
11 => 0 or null

This is closer, just have to get the right count value for each array key

function count_uploads_perMonth(){

  $monthsNum = array('1','2','3','4','5','6','7','8','9','10','11','12');
  $this->db->select("month_uploaded as cnt");
  $this->db->where_in('month_uploaded',$monthsNum);
  $this->db->group_by('month_uploaded');
  $query = $this->db->get($this->table_par);

  $count_uploads = array_fill(1, 12, 0);

    foreach( $query->result() as $row ){

      $count_uploads[$row->cnt] = $row->cnt;

    }
    var_dump($count_uploads);

}

Output: 

 array (size=12)
 1 => int 0
 2 => int 0
 3 => int 0
 4 => int 0
 5 => int 0
 6 => string '6' (length=1)  --- value should be 1 and length is 1
 7 => string '7' (length=1)  --- value should be 4 and length is 4
 8 => int 0
 9 => int 0
 10 => int 0
 11 => int 0
 12 => int 0

Upvotes: 5

Views: 894

Answers (5)

mickmackusa
mickmackusa

Reputation: 48011

You can join a derived table containing all months in a year so that every result set has exactly 12 rows. I don't see the need for the where_in() since a year only has values from 1 through 12. I am going to assume that your source table has a column called id for the sake of accurate counting.

If any WHERE clause is needed, I'd say you should be filtering by year (if that column exists in your table).

return array_column(
    $this->db
        ->select('month, COUNT(tablepar.id) count')
        ->from('(' . implode(' UNION ALL ', substr_replace(['1 month'] + range(1, 12), 'SELECT ', 0, 0)) . ') months')
        ->join($this->table_par tablepar, 'month_uploaded = month', 'left')
        ->group_by('month')
        ->order_by('month')
        ->result(),
    'count',
    'month'
);

Rendered SQL:

SELECT `month`, COUNT(tablepar.id) count
FROM (SELECT 1 month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months
LEFT JOIN `your_table_name` `tablepar` ON `month_uploaded` = `month`
GROUP BY `month`
ORDER BY `month`

Or if you want to populate the array in PHP-land after a simpler query:

return array_replace(
    array_fill_keys(range(1, 12), 0),
    array_column(
        $this->db
            ->select('month_uploaded, COUNT(1) count')
            ->group_by('month_uploaded')
            ->get($this->table_par)
            ->result(),
        'count',
        'month_upload'
    );
}

Upvotes: 0

Narendrasingh Sisodia
Narendrasingh Sisodia

Reputation: 21422

I'm not sure but I think what you need over here is array_count_values. You just make an array of result from your query using ->get()->result_array();

$array = range(1,12);
$result = array(7,7,7,6);
$final = array_count_values($result);
$final_array = array();
foreach($array as $value){
    $final_array[$value] = array_key_exists($value ,$final) ? $final[$value] : 0;
}
print_r($final_array);

Edited :

As per your resulted array you can update your array as

$arr = array(0 => array ('month_uploaded' =>  '7'),  1 => array ('month_uploaded' =>  '7'),  2 => array ('month_uploaded' =>  '7'), 3 => array ('month_uploaded' =>  '7'),  4 => array ('month_uploaded' =>  '6' ));
$result = array_count_values(array_column($arr,'month_uploaded'));
$final_array = array();
foreach($array as $value){
    $final_array[$value] = array_key_exists($value ,$result) ? $result[$value] : 0;
}
print_r($final_array);

Fiddle

Upvotes: 1

splash58
splash58

Reputation: 26153

Change SQL query to

SELECT month_uploaded-1 `month`, COUNT(*) `count` FROM table_name GROUP BY month_uploaded

you will receive (5,1), (6,4). Then

// Prepare array with zeros
$count_uploads = array_fill(0, 12, 0);

foreach( $query->result() as $row )
   $count_uploads[$row->month] = $row->count;

Upvotes: 1

shankar kumar
shankar kumar

Reputation: 648

Use following functions and get your result.

function count_uploads_perMonth(){

$monthsNum = array('1','2','3','4','5','6','7','8','9','10','11','12');
$this->db->select("count(month_uploaded) as cnt");
$this->db->where_in('month_uploaded',$monthsNum);
$this->db->group_by('month_uploaded');
$query = $this->db->get('$this->table_par');


  foreach( $query->result() as $row ){

    $count_uploads[] = $row->cnt;

  }
  echo "<pre>";
  print_r($count_uploads);

 }

Upvotes: 1

Pupil
Pupil

Reputation: 23958

Append the count to array with months as keys.

$count_uploads[$row->month_uploaded][] = $row->month_uploaded;

Now, you will get array with month as key and appended values as values.

echo '<pre>';
print_r($count_uploads);
echo '</pre>';

Sample array

array(
  3 => array(3, 3),
  4 = array()
)

Upvotes: 1

Related Questions