EllisLab text mark
Advanced Search
     
How do I sort database results using multiple columns?
 
danfloun
Posted: 05 September 2007 07:26 PM   [ Ignore ]
Joined: 2007-08-09
20 posts

As the question states, how can I order by first second and third column ascending.

At present I use

$this->db->select('id, company, first_name, last_name, landline_tel, mobile_tel, fax_tel, email');
        $this->db->orderby("company", "asc");
        $query = $this->db->get('clients');
        return $query->result(); 

but this only orders by the company column and I would like to order by the last_name column as a second sort.

Thanks

Danny
 
John_Betong
Posted: 05 September 2007 10:26 PM   [ Ignore ]   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2006-12-26
727 posts

Hi Danfloun,

In the user manual (Class Reference/Dababase Class/Active Record Class) it states:

$this->db->orderby();

Lets you set an ORDER BY clause.
The first parameter contains the name of the column you would like to order by.
The second parameter lets you set the direction of the result.
Options are asc or desc or RAND()

$this->db->orderby(“title”, “desc”);

// Produces: ORDER BY title DESC


You can also pass your own string in the first parameter:

$this->db->orderby(‘title desc, name asc’);

// Produces: ORDER BY title DESC, name ASC

 
So your query:

$this->db->orderby("company", "asc");

// try 
   $this->db->orderby("company ASC, last_name ASC"); 


After using the query check it by using the following:

$this->db->last_query();
   die; 
 Signature 

Joke of the day - Bulletin Board Ideas     (ongoing development site)

My Hippy Trail    Source code   

Latest Project
 
danfloun
Posted: 06 September 2007 04:41 PM   [ Ignore ]   [ # 2 ]   [ Rating: 0 ]
Joined: 2007-08-09
20 posts

Thanks John_Betong,

May I ask what the checking is for?
I understand that

$this->db->last_query() 

Returns the last query, so I assume the above code just checks to see if the query was successful! or more to the point, there was a result returned?

Is that right?  Either way, why should I bother with that bit?

Thanks for you time.

Danny
 
John_Betong
Posted: 07 September 2007 01:33 AM   [ Ignore ]   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2006-12-26
727 posts

Hi Danny,

It is not necessary to use $this->db->last_query(). It is just a means of displaying the complete SQL statement.

CodeIgniter makes it easy to create SQL statement strings. I find it is sometimes better to see the complete statement especially when the result is not what was expected.

If you are not familiar with SQL then final SQL statement would not be much use.

Just ignore it.

Cheers,

John_Betong
 

 Signature 

Joke of the day - Bulletin Board Ideas     (ongoing development site)

My Hippy Trail    Source code   

Latest Project