How to order by first name (SQL) in an exploded string

Question

I have the following code to search for a name in a field that has all three name so I have been forced to explode the string for better results as follows

$search_terms = explode(" ", $key);
$count = count($search_terms);
$tick= 0;
$query = "";
for($i = 0; $i < $count; $i++) {
    $tick++;
    $query .= "SELECT * ";
    $query .= "FROM agents ";
    $query .= "WHERE names ";
    $query .= "LIKE '%".$search_terms[$i]."%' ";
    $query .= "OR company LIKE '%".$search_terms[$i]."%' ";
    if($tick != $count)
    $query .= " UNION ";
}
    $query .="ORDER BY names ASC ";

The problem is that once a search query such as Lawrence Gabriel and my database does indeed have Lawrence Gabriel but also Edward Gabriel, I will get the Edward result listed before Lawrence. I'd like to have all 'Gabriels', so to speak, listed but for my results to be ordered by the first name typed into the search box. This would have been easier if first name, second name were in individual columns.

How can I achieve the desired result?

I'd like to retrieve all columns from the table, hence the SELECT * — andromeda, Mar 19 '14 at 15:01
Check this: [Mysql: Order by like?](http://stackoverflow.com/questions/3609166/mysql-order-by-like) — AbraCadaver, Mar 19 '14 at 15:01

score 2 · Accepted Answer · answered Mar 19 '14 at 15:02

2

try this

   ORDER BY SUBSTRING_INDEX(names, ' ', 1) ASC

this will order by the first name before the space

Demo

you can use it like that also:

 SELECT id, names,  SUBSTRING_INDEX(names, ' ', 1) name
 FROM agents 
 WHERE names 
 LIKE '%".$search_terms[$i]."%' 
 OR company LIKE '%".$search_terms[$i]."%'
 ORDER BY name asc

DEMO

answered Mar 19 '14 at 15:02

echo_Me

37,078
5
58
78

This actually works but I had to play around with the count and changed it to -2 for the desired result. Thanks very much! – andromeda Mar 19 '14 at 15:14
you welcome ,yes you can play around it as you like :) – echo_Me Mar 19 '14 at 15:15

How to order by first name (SQL) in an exploded string

1 Answers1