CodeIgniter Forums

Full Version: how to use order by codeigniter
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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 

}
 and i have these two scripts
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;

amount_m.php
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
order_by should be after all where commands and should be the last command before retrieving the query ...
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.
(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.
(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); 
 but no luck
(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
(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.
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
(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)); 
 i removed the joide function

then i used array array_merge and array_filter and it worked