Trying to simplify a series of redundant codeigniter Active Record queries

Hi,
Below are a series of very similar active record queries I'm running. I'd like to simplify them as it seems quite redundant but not sure how? They're all getting some stats for the past 30 days.

First off, any idea how to make it 'stats for the current month' not the past 30 days? My 'created' column is a datetime stamp.

Query 1 and 5 are related. 1 gets the total amount of trades and the fifth query gets their total dollar value. Seems 'possible' to combine those 2?

Secondly, the 'CURRENT, DEAL AND NO DEAL' (queries 2, 3, and 4) are the same column. Be much better if I could somehow simply count how many of each in one query instead of having to run 3 queries on the same column to get how many of each.

And lastly they all use the same time interval... seems redundant to reiterate it 5 times over?
// Get Stats for frontend

function get_stats($data) {
	
	$parent_id = $data['parent_id'];
	
// get total amount of trades
	$this->db->select('id');
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');  
	$query = $this->db->get();
	$data['total'] = $query->num_rows();
	
//get amount of trades whose status is CURRENT	
	$this->db->select('id');
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('status', 'CURRENT');
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');  
	$query = $this->db->get();
	$data['current'] = $query->num_rows();
	
//get amount of trades whose status is DEAL	
	$this->db->select('id');
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('status', 'DEAL');
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');
	$query = $this->db->get();
	$data['deal'] = $query->num_rows();
	
//get amount of trades whose status is NO DEAL	
	$this->db->select('id');
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('status', 'NO DEAL');
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');
	$query = $this->db->get();
	$data['nodeal'] = $query->num_rows();

//total dollar amount for all trades combined
	$this->db->select('SUM(finalTradeValue) AS tradeTotal', FALSE);
	$this->db->from('trades');
	$this->db->where('parent_id', $parent_id);
	$this->db->where('created > DATE_SUB(CURDATE(),INTERVAL 30 DAY)');
	$query = $this->db->get();
	$data['tradeamount'] = $query->row_array();

	return $data;
}

Open in new window

Any help is much appreciated.
tj
LVL 1
tjyoungAsked:
Who is Participating?
 
Chris StanyonConnect With a Mentor Commented:
Have a look at this. I think you'll need 2 queries - one for the count and value and another for the STATUS breakdown:

$parentId = $data['parent_id'];
$today = new DateTime();
$monthStart = new DateTime('First Day of this Month');

$this->db
	->select('COUNT(id) as count')->select('SUM(finalTradeValue) as value')
	->from('trades')
	->where('parent_id', $parentId)->where('created >=', $monthStart->format('Y-m-d'))->where('created <=', $today->format('Y-m-d'));	

//get 1 row with 2 columns - count and total
$result = $this->db->get()->row();
echo $result->count;
echo $result->value;




$this->db
	->select('status')->select('COUNT(id) AS count')->select('SUM(finalTradeValue) AS value')
	->from('trades')
	->where('parent_id', $parentId)->where('created >=', $monthStart->format('Y-m-d'))->where('created <=', $today->format('Y-m-d'))
	->group_by('status');
	
//get a row for each status, along with the count and value
$result = $this->db->get()->result();
foreach ($result as $row):
	echo $row->status;
	echo $row->count;
	echo $row->value;
endforeach;

Open in new window

In theory, you could just use the last query and in the foreach loop, sum the count and the value manually:

$totalValue = 0;
$totalCount = 0;
foreach ($result as $row):
	echo $row->status;
	echo $row->count;
	echo $row->value;
	$totalValue = $totalValue + $row->value;
	$totalCount = $totalCount + $row->count;
endforeach;

echo $totalValue;
echo $totalCount;

Open in new window

0
 
tjyoungAuthor Commented:
Hi
Is the first day of month
Php 5.3+?
I'm stuck at 5.2
Way to express that for 5.2?
Driving to work so my response is kinda brief :)
0
 
Chris StanyonCommented:
Ahh. Yeah - that's 5.3. In 5.2, do something like:

$monthStart = new DateTime(date('m-01-Y'));

Open in new window

0
 
tjyoungAuthor Commented:
Thanks again Chris.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.