saleena saleena - 1 month ago 5
MySQL Question

group rows based on a column codeigniter

I want to group rows based on a column .I attached my table
I tried,my problem is that,when using group_by,it wil return only one row of each user. i want whole data from this table group by user_id
Model

public function get_pdf_print( $start_date, $end_date) {
$this->db->select('*');
$this->db->from('jil_invoices');
$this->db->group_by('jil_invoices.inv_userid');
$this->db->where('jil_invoices.inv_dated >=', $start_date);
$this->db->where('jil_invoices.inv_dated <=', $end_date);
$query = $this->db->get();
return $query->result();
}


this is my table structure

Answer

If you want to use GROUP BY with userid column than note that it will not return the all rows.

I suggest you to get all rows and than use PHP for grouping purpose something like:

In your Model:

public function get_pdf_print($start_date, $end_date) {
    $this->db->select('*');
    $this->db->from('jil_invoices');
    $this->db->where('jil_invoices.inv_dated >=', $start_date);
    $this->db->where('jil_invoices.inv_dated <=', $end_date);
    $query = $this->db->get();
    return $query->result_array(); // here i am using result_array function will return result into array.
}

In your controller:

$records = $this->model_yourmodel->get_pdf_print($startDate,$endDate);
$userData = array();
foreach ($records as $key => $value) {
    $userData[$value['inv_userid']][] = $value; // get all record against each user
}
Comments