How to sort MySQL results alphabetically but with search term match first using CodeIgniter ActiveRecord?

Question

Let say we have 4 items in a table:

1. Michel Jordan
2. Tom Mark Jordan
3. Jordan John
4. Adam Jordan Robert

The search term is "Jordan", how can i get the results sorted alphabetically but with search term match first like this:

1. Jordan John
2. Michel Jordan
3. Tom Jordan Robert
4. Adam Mark Jordan

Im using this code, but not getting what i want:

$this->db->select('id, name');
$this->db->from('users');
$this->db->like('name', $search_term);
$this->db->order_by('name', 'asc');
$query = $this->db->get();

Answer 1

Try this:

SELECT id, fullName 
FROM test 
ORDER BY FIND_IN_SET('Jordan', REPLACE(fullName, ' ', ',')), fullName;

Check this link SQL FIDDLE DEMO

OUTPUT

| ID |          FULLNAME |
|----|-------------------|
|  1 |       Jordan John |
|  2 |     Michel Jordan |
|  4 | Tom Jordan Robert |
|  3 |  Adam Mark Jordan |

Answer 2

You can try this:

$this->db
     ->select('id, fullName')
     ->from('test')
     ->order_by("FIND_IN_SET('Jordan', REPLACE(fullName, ' ', ',')) , fullName");
$query = $this->db->get();