Joining Multiple Queries into 1 - El Forum - 10-13-2010
[eluser]CI_Newb[/eluser]
I have a submenu item in my application that does some really simple queries to display the total rows for 6 different items.
All query the same db with slight changes.
Model
Code: <?php
class Sub_model extends Model
{
function user_stats_today()
{
$cur_date = date ('Y-m-d');
$this->db->select('date');
$this->db->where('username', $this->session->userdata('username'));
$this->db->where('date', $cur_date);
$data = $this->db->get('my_db');
return $data;
}
function user_stats_yesterday()
{
$yesterday = date ("Y-m-d", time() -86400);
$this->db->select('date');
$this->db->where('username', $this->session->userdata('username'));
$this->db->where('date', $yesterday);
$data = $this->db->get('my_db');
return $data;
}
function user_stats_resolved()
{
$cur_date = date ('Y-m-d');
$this->db->select('date');
$this->db->where('username', $this->session->userdata('username'));
$this->db->where('date', $cur_date);
$this->db->where('resolution', 'Resolved');
$data = $this->db->get('my_db');
return $data;
}
function user_stats_dispatched()
{
$cur_date = date ('Y-m-d');
$this->db->select('date');
$this->db->where('username', $this->session->userdata('username'));
$this->db->where('date', $cur_date);
$this->db->where('resolution', 'Dispatched');
$data = $this->db->get('my_db');
return $data;
}
function user_stats_abandons()
{
$cur_date = date ('Y-m-d');
$this->db->select('date');
$this->db->where('username', $this->session->userdata('username'));
$this->db->where('date', $cur_date);
$this->db->where('level1', 'Abandon');
$data = $this->db->get('my_db');
return $data;
}
function user_stats_month()
{
$year_month = date ('Y-m');
$this->db->select('date');
$this->db->where('username', $this->session->userdata('username'));
$this->db->like('date', $year_month);
$data = $this->db->get('my_db');
return $data;
}
}
Controller
Code: function index()
{
$data = array();
$data['us_today'] = $this->sub_model->user_stats_today();
$data['us_yesterday'] = $this->sub_model->user_stats_yesterday();
$data['us_resolved'] = $this->sub_model->user_stats_resolved();
$data['us_dispatched'] = $this->sub_model->user_stats_dispatched();
$data['us_abandons'] = $this->sub_model->user_stats_abandons();
$data['us_month'] = $this->sub_model->user_stats_month();
$this->load->view(submenu_view, $data);
}
View
Code: <b>Today</b> <?php echo $us_today->num_rows(); ?>
<br>
<b>Resolved</b> <?php echo $us_resolved->num_rows(); ?>
<br>
<b>Dispatched</b> <?php echo $us_dispatched->num_rows(); ?>
<br>
<b>Abandons</b> <?php echo $us_abandons->num_rows(); ?>
<br>
<b>Yesterday</b> <?php echo $us_yesterday->num_rows(); ?>
<br>
<b><?php echo $cur_month_name; ?></b> <?php echo $us_month->num_rows(); ?>
This looks sloppy and overdone. How can I better optimize the code and queries?
Joining Multiple Queries into 1 - El Forum - 10-13-2010
[eluser]umefarooq[/eluser]
as you query only one table just create a single query with sub queries it will work great
Code: $user_name = $this->session->userdata('username');
$cur_date = date ('Y-m-d');
$this->db->select('date');
$this->db->select('(select date from my_db where date = "'.$cur_date.'" and username = "'.$user_name.'") as yesterday',TRUE);
$this->db->select('(select date from my_db where date = "'.$cur_date.'" and username = "'.$user_name.'") as stat_month',TRUE);
$this->db->where('username', $user_name );
$this->db->where('date', $cur_date);
$data = $this->db->get('my_db');
return $data;
and so on only one sub query will get you all recode if you query only one table for all records
Joining Multiple Queries into 1 - El Forum - 10-13-2010
[eluser]CI_Newb[/eluser]
Good stuffs umefarooq, so with this master query, how do I separate the data to display results from each sub query?
Joining Multiple Queries into 1 - El Forum - 10-15-2010
[eluser]CI_Newb[/eluser]
Issue resolved. Solution was easier than I expected
Thanks again umefarooq!
Joining Multiple Queries into 1 - El Forum - 10-15-2010
[eluser]techgnome[/eluser]
Wait... is all you are interested in is just the COUNTS? Or do you actually use the data? If that's the case, there might still be a better way too.
-tg
Joining Multiple Queries into 1 - El Forum - 10-15-2010
[eluser]CI_Newb[/eluser]
Ya I'm just needing a count and displaying the total from each sub query
Joining Multiple Queries into 1 - El Forum - 10-18-2010
[eluser]CI_Newb[/eluser]
[quote author="techgnome" date="1287183065"]Wait... is all you are interested in is just the COUNTS? Or do you actually use the data? If that's the case, there might still be a better way too.
-tg[/quote]
What was your idea techgnome? I'm all ears!
|