CODEWITHSUNDEEP
phpmysql
John M
John M

Reputation: 1

Search for Firstname or Last name in column formatted as "Firstname Lastname"

In a table I'm working with the full_name column has the format "Firstname Lastname"

Eg. "John West"

I'm using this: 

$q="John";

$sql = "SELECT full_name  FROM wp_users where full_name like '$q%' order by display_name ASC";

That works ok and "John West" is returned.

But if I make $q equal to "West" and try to search by the surname then nothing gets returned. Is there an mysql statement that I can use to do this better?

Upvotes: 0

Views: 95

Answers (3)

Spock
Spock

Reputation: 327

Have you tried with

$q="John";

$sql = "SELECT full_name  FROM wp_users where full_name like '$q%' or full_name like '$%q'  order by display_name ASC";

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

try using a proper wildchar concat for your condition

$sql = "SELECT full_name  
          FROM wp_users where full_name like concat('%', $q,'%') 
          ORDER BY display_name ASC";

or 

$q = '%John%';

$sql = "SELECT full_name  
          FROM wp_users where full_name like '$q' 
          ORDER BY display_name ASC";

Upvotes: 1

ATechGuy
ATechGuy

Reputation: 1270

put percentage on both sides

    $q="John";

$sql = "SELECT full_name  FROM wp_users where full_name like '%$q%' order by display_name ASC";

Upvotes: 0

Related Questions