'Greater than' operator is working but not 'lesser than' in Model class

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?

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();
} 


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.

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

Happy to be of assistance

Please check In your database "infoID" datatype if data type is int than only work if datatype is not int or float than you must be need to convert first it to int for example "CAST(infoID) AS INT"

Hire Codeigniter Developer