CODEWITHSUNDEEP
codeignitercodeigniter-2
black_belt
black_belt

Reputation: 6799

Codeigniter: fetching records and sum

I have the following table and I am trying to display the sum of p_total along with all other records in the table. I have managed to display the total amount but if I display it, only values of the fist row of the table shows up. Although the sum of p_total is showing correctly as 600 .

Could you please tell me where is the problem in my code below:

Thanks in advance :)

My DB Table:

 p_id   p_name    p_quantity    p_rate   p_total       user_id
  1     Pepsi        12           30       360            1
  2     Hot Breads   12           20       240            1

I have the following code in my model

$this->db->select('*,SUM(temporary_table.p_total AS Total');
$this->db->from('temporary_table');
$this->db->where('user_id',$user_id);                                   
$getData = $this->db->get('');

if($getData->num_rows() > 0) {
   return $getData->result_array();
}

else { 
   return null;
}

This is my controller:

$this->load->model('mod_sales');
$data['records']= $this->mod_sales->add_to_temporary_table($user_id);

My View:

 foreach ($records as $row)
 {   
   <td><?php echo $row['p_name'];?></td>
   <td><?php echo $row['p_quantity'];?></td>
   <td><?php echo $row['p_rate'];?></td>
   <td><?php echo $row['p_total'];?></td>
 }

<?php echo $row['Total'];?>

Upvotes: 4

Views: 25993

Answers (3)

Limitless isa
Limitless isa

Reputation: 3802

You have a total and separate list.

$this->db->select_sum('total_price');
$this->db->from('order');
$this->db->where('dates BETWEEN DATE_ADD(NOW(), INTERVAL -7 DAY) AND NOW() ');
$query=$this->db->get();
$data['total']=$query->row()->total_price;

$this->db->select('*');
$this->db->from('order');
$this->db->where('dates BETWEEN DATE_ADD(NOW(), INTERVAL -7 DAY) AND NOW()');
$query=$this->db->get();
$data['count']=$query->num_rows();

Output:

 Array ( 

 [total] => 2759 

 [count] => 5 

)

Upvotes: 5

Sergey Tsibel
Sergey Tsibel

Reputation: 1625

In your View: 

$total_sum=0;

foreach ($records as $row){

 <td><?php echo $row['p_name'];?></td>
 <td><?php echo $row['p_quantity'];?></td>
 <td><?php echo $row['p_rate'];?></td>
 <td><?php echo $row['p_total'];?></td>

 $total_sum+=$row['p_total'];
}

<?php echo $total_sum;?>

Upvotes: 3

scriptin
scriptin

Reputation: 3120

You're missing GROUP BY clause.

As I understand, you want this:

SELECT *, SUM(p_total) FROM temporary_table WHERE user_id = ... GROUP BY p_id

Not familiar with CodeIgniter, but guessing you need this line after "where":

$this->db->group_by('p_id');

Upvotes: 1

Related Questions