Sort a Query using a variable?

[eluser]internut[/eluser]
Second question on the night:

How can I place a var in a sort query. I'm getting the table row name i want to sort by via:

Code:

$sort = $this->uri->segment(3);

and want to use it in the query call to determine how to sort:

Code:

$data['query'] = $this->db->query('SELECT * FROM users  ORDER BY last_name ASC');

where "last_name" is above - i want to use the var $sort

[eluser]internut[/eluser]
Got it done using:

Code:

$sort = $this->uri->segment(3);
        $sql = "SELECT * FROM `users` ORDER BY $sort ASC";
        $data['query'] = $this->db->query($sql);

better way?

[eluser]xwero[/eluser]

Code:

$this->db->order_by($this->uri->segment(3),'asc')->get('users');

[eluser]internut[/eluser]
Lab thanks, interesting.

I did have to do a check incase segment(3) was not available so I'm using:

Code:

if($this->uri->segment(3)) { $sort = $this->uri->segment(3); }
else { $sort = "last_name"; }

$sql = "SELECT * FROM `users` ORDER BY $sort ASC";

based on what you provided should be able to make this two lines of code.

[eluser]internut[/eluser]

Code:

$data['query'] = $this->db->order_by($this->uri->segment(3),'asc')->get('users');

is a b-e-a-utiful thing. Trying how I can work it if segment(3) is not set tho. Is there a way within ORDER BY to do an OR ?

[eluser]xwero[/eluser]

Code:

$sort = ($this->uri->segment(3))?$this->uri->segment(3):"last_name";
$this->db->order_by($sort,'asc')->get('users');

[eluser]internut[/eluser]
very nice and excellent help!

this is what i just went from:

Code:

if($this->uri->segment(3)) { $sort = $this->uri->segment(3); }
else { $sort = "last_name"; }  // default sort
$data['query'] = $this->db->order_by($sort,'asc')->get('users');

to

Code:

$sort = ($this->uri->segment(3))?$this->uri->segment(3):"last_name";
$data['query'] = $this->db->order_by($sort,'asc')->get('users');

just a question why segment(3) needs to be twice above?

[eluser]Chris Newton[/eluser]
The code above is alternate PHP syntax (short)

This is how it would read with standard PHP if/else statements

Code:

if ($this->uri->segment(3)){
$sort=$this->uri->segment(3);
}
else
{
$sort="last_name";
}

[eluser]ray73864[/eluser]
How would you capture the error if the column doesn't exist in the table?

I have the problem where some stupid person might get all medievel and change the order_by parameter to a column name that doesn't exist (the function has a default value, but that doesn't help if the person types in a bad column) i need it to default to either the default value or to that of a column i know exists.

I would like to do something like '$this->db->order_by($orderby,$direction[,<default stuff>])' or maybe some other way.

[eluser]valarkin[/eluser]
I think you are looking for something along these lines...

Code:

if ($this->uri->segment(3) && $this->db->field_exists($this->uri->segment(3), 'table_name')){
$sort=$this->uri->segment(3);
}
else
{
$sort="last_name";
}

I had it short hand, but it was way to wide for the forums

What you were looking for this:
$this->db->field_exists('field_name', 'table_name');