• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
'Greater than' operator is working but not 'lesser than' in Model class

#1
This is my example table:

Code:
infoID   userNAME
1         Alpha
2         Bravo
3         Charlie
4         Delta
5         Echo

This is my model:
Code:
public function get_next_info() {
    $a = $this->input->post('infoID');        
    $this->db->where('infoID >', $a);        
    $query = $this->db->get('info');                 
       return $query->row_array();            
}
    
public function get_previous_info() {
    $a = $this->input->post('infoID');        
    $this->db->where('infoID <', $a);        
    $query = $this->db->get('info');                 
       return $query->row_array();
}
 
My issue is 'get_next_info' is working fine in my view,when a 'NEXT' button is pressed,the data is able to display from 1 to 2 to 3 and so on,But when a 'PREVIOUS' button is pressed,the data straight go back id no1,instead of  3 to 2 to 1 as intended.

Anything wrong with my 'get_previous_info' query?
Reply

#2
There's a simple explanation to this, and very simple fix.

If you don't order your query result, it will be ordered the same way it went in.

Lets assume you have items 1 2 3 and 4, the first matching item ID > 2 would be ID = 3, but the first matching result for ID < 4 would be ID = 1, because that's the first SQL server can find.

What you want to do is reverse results when you go backwards (4 3 2 1).

PHP Code:
public function get_previous_info() {
    
$a $this->input->post('infoID');        
    
$this->db->where('infoID <'$a);
    
$this->db->order_by('infoID''DESC'); // sort in reverse order
    
$query $this->db->get('info');                 
       return 
$query->row_array();


Because you only want the first matching item, I would also add limit to both methods. Right now the query will return all matching results from DB, and if you get to ID 1000, that's just unnecessary data your DB server has to return.

PHP Code:
public function get_previous_info() {
    
$a $this->input->post('infoID');        
    
$this->db->where('infoID <'$a);
    
$this->db->order_by('infoID''DESC');
    
$this->db->limit(1); // only return first matching result
    
$query $this->db->get('info');                 
       return 
$query->row_array();

Reply

#3
(07-11-2018, 03:45 AM)Pertti Wrote: There's a simple explanation to this, and very simple fix.

If you don't order your query result, it will be ordered the same way it went in.

Lets assume you have items 1 2 3 and 4, the first matching item ID > 2 would be ID = 3, but the first matching result for ID < 4 would be ID = 1, because that's the first SQL server can find.

What you want to do is reverse results when you go backwards (4 3 2 1).

PHP Code:
public function get_previous_info() {
 
   $a $this->input->post('infoID');        
    $this
->db->where('infoID <'$a);
 
   $this->db->order_by('infoID''DESC'); // sort in reverse order
 
   $query $this->db->get('info');                 
       
return $query->row_array();


Because you only want the first matching item, I would also add limit to both methods. Right now the query will return all matching results from DB, and if you get to ID 1000, that's just unnecessary data your DB server has to return.

PHP Code:
public function get_previous_info() {
 
   $a $this->input->post('infoID');        
    $this
->db->where('infoID <'$a);
 
   $this->db->order_by('infoID''DESC');
 
   $this->db->limit(1); // only return first matching result
 
   $query $this->db->get('info');                 
       
return $query->row_array();


Hi Pertti,thanks for the help!Work like a charm now for both methods.
Reply

#4
(07-11-2018, 04:56 AM)Leolabs Wrote: Hi Pertti,thanks for the help!Work like a charm now for both methods.

Happy to be of assistance Cool
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2018 MyBB Group.