Joining Multiple Queries into 1

[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> &lt;?php echo $us_today->num_rows(); ?&gt;
<br>
<b>Resolved</b> &lt;?php echo $us_resolved->num_rows(); ?&gt;
<br>
<b>Dispatched</b> &lt;?php echo $us_dispatched->num_rows(); ?&gt;
<br>
<b>Abandons</b> &lt;?php echo $us_abandons->num_rows(); ?&gt;
<br>
<b>Yesterday</b> &lt;?php echo $us_yesterday->num_rows(); ?&gt;
<br>
<b>&lt;?php echo $cur_month_name; ?&gt;</b> &lt;?php echo $us_month->num_rows(); ?&gt;

This looks sloppy and overdone. How can I better optimize the code and queries?

[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

[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?

[eluser]CI_Newb[/eluser]
Issue resolved. Solution was easier than I expected

Thanks again umefarooq!

[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

[eluser]CI_Newb[/eluser]
Ya I'm just needing a count and displaying the total from each sub query

[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!