Solved

Trying to simplify a series of redundant codeigniter Active Record queries

Posted on 2013-10-29
4
1,071 Views
Last Modified: 2013-10-29
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
0
Comment
Question by:tjyoung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39608237
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
 
LVL 1

Author Comment

by:tjyoung
ID: 39608252
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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39608271
Ahh. Yeah - that's 5.3. In 5.2, do something like:

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

Open in new window

0
 
LVL 1

Author Closing Comment

by:tjyoung
ID: 39608316
Thanks again Chris.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 37
PHP 5.6 and 7.x 4 39
form validation - make sure at least 1 checkbox is selected 18 65
Get data from two MySQL tables 6 22
There is a huge demand for CodeIgniter among the PHP web developers due to its dynamic features and benefits these days. It is one of most popular and agile open source PHP framework for creating robust web applications in PHP web development field.…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question