+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: how to use order by codeigniter (/showthread.php?tid=68298)
how to use order by codeigniter - kayinja.denis - 06-21-2017
am trying to use order by but i don't get all the results in table.
i have payment table
Code:
payments={
paymentid|academicID| studentID| invoiceID| amount
1 | 1 | 1 | 1 | 2000
1 | 1 | 1 | 2 | 300
1 | 1 | 1 | 1 | 5
1 | 1 | 1 | 3 | 600
1 | 1 | 1 | 3 | 10
}MY_model.php
PHP Code:
function get_order_by($array=NULL) {
if($array != NULL) {
$this->db->select()->from($this->_table_name)->order_by($this->_order_by)->where($array);
$query = $this->db->get();
return $query->result();
} else {
$this->db->select()->from($this->_table_name)->order_by($this->_order_by);
$query = $this->db->get();
return $query->result();
}
}
action_helper.php
PHP Code:
function joide($array, $value, $key=NULL) {
$returnArray = array();
if(count($array)) {
foreach ($array as $item) {
if($key != NULL) {
$returnArray[$item->$key] = strtolower($value) == 'obj' ? $item : $item->$value;
} else {
$returnArray[] = $item->$value;
}
}
}
return $returnArray;
}
PHP Code:
function get_order_by_amount($array=NULL) {
$query = parent::get_order_by($array);
return $query;
}
controller.php
PHP Code:
$payments = joide($this->amount_m->get_order_by_amount(['academicID' => $academicID]), 'obj', 'invoiceID');
$this->data['amount'] = $payments;
so when i run <?php dd($mummy) ?> i don't get all the results, i mean it won't get the amount = 3 and 10 where invoiceid =1 and 3 respectively but it returns the 2000, 3000 and 600, it seems it doesn't return the amount where invoiceID is the same like for 1 and 3
RE: how to use order by codeigniter - InsiteFX - 06-21-2017
order_by should be after all where commands and should be the last command before retrieving the query ...
RE: how to use order by codeigniter - Martin7483 - 06-21-2017
The order in which you call the methods does not matter. Query Builder sorts them out for you and will place everything in the correct order.
This is your problem
PHP Code:
$returnArray[$item->$key] = strtolower($value) == 'obj' ? $item : $item->$value;
You are overwriting values in the array because the same column is returned more than once. So this way only the last value is returned.
RE: how to use order by codeigniter - InsiteFX - 06-21-2017
(06-21-2017, 03:48 AM)Martin7483 Wrote: The order in which you call the methods does not matter. Query Builder sorts them out for you and will place everything in the correct order.
This is your problem
PHP Code:$returnArray[$item->$key] = strtolower($value) == 'obj' ? $item : $item->$value;
You are overwriting values in the array because the same column is returned more than once. So this way only the last value is returned.
This is like teaching someone a bad habit, try running that query in raw MySQL.
RE: how to use order by codeigniter - kayinja.denis - 06-21-2017
(06-21-2017, 03:19 AM)InsiteFX Wrote: order_by should be after all where commands and should be the last command before retrieving the query ...
thanks for reply,
i changed my order
PHP Code:
from;
$this->db->select()->from($this->_table_name)->order_by($this->_order_by)->where($array);
to
$this->db->select()->from($this->_table_name)->where($array)->order_by($this->_order_by);
RE: how to use order by codeigniter - kayinja.denis - 06-21-2017
(06-21-2017, 03:48 AM)Martin7483 Wrote: The order in which you call the methods does not matter. Query Builder sorts them out for you and will place everything in the correct order.
This is your problem
PHP Code:$returnArray[$item->$key] = strtolower($value) == 'obj' ? $item : $item->$value;
You are overwriting values in the array because the same column is returned more than once. So this way only the last value is returned.
I agree that's where the problem, if i update this
PHP Code:
$payments = $this->payment_m->get_order_by_amount(array('schoolyearID' => $schoolyearID));
i get all the the results, but again i need to define the invoiceID cos have to use it in getting the total amount
RE: how to use order by codeigniter - Martin7483 - 06-21-2017
(06-21-2017, 04:05 AM)InsiteFX Wrote: This is like teaching someone a bad habit, try running that query in raw MySQL.
Agreed, you should indeed call the query methods in the correct order. Makes for better reading of the constructed query.
But that had nothing to do with his problem. The problem is in his result parser.
RE: how to use order by codeigniter - Martin7483 - 06-21-2017
Why not just use the unique id of each record as the key for your array?
PHP Code:
$returnArray[$item->id] = strtolower($value) == 'obj' ? $item : $item->$value;
RE: how to use order by codeigniter - kayinja.denis - 06-22-2017
(06-21-2017, 05:33 AM)Martin7483 Wrote: Why not just use the unique id of each record as the key for your array?
PHP Code:$returnArray[$item->id] = strtolower($value) == 'obj' ? $item : $item->$value;
hello Martin,
actually i have used
PHP Code:
$payments = $this->payment_m->get_order_by_amount(array('schoolyearID' => $schoolyearID));
then i used array array_merge and array_filter and it worked