CODEWITHSUNDEEP
phpmysqlsql
Cepheus
Cepheus

Reputation: 4903

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

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?

Upvotes: 2

Views: 122

Answers (1)

echo_Me
echo_Me

Reputation: 37233

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

Upvotes: 2

Related Questions