join 2 fields in mysql to search both at the same time

Question

I am trying to search the field firstname and lastname for a keyword

$q1 = strtolower($_GET["q"]);
$q=str_replace(" ","%",$q1);
$sql = "select DISTINCT users.*, user_id FROM users WHERE $email_filter 
firstname LIKE '%$q%' OR lastname LIKE '%$q%' ORDER BY lastname";
$rsd = mysql_query($sql);
while($rs = mysql_fetch_array($rsd)) { echo $results }

this is what I have so far, issue is if you use John Doe as an example once you type John it finds it, doe it finds it, but john doe ... no results

You need to concatenate the first and lastname fields, and compare *that* to your search string, as per [Using mysql concat() in WHERE clause?](http://stackoverflow.com/questions/303679/using-mysql-concat-in-where-clause) — Dan J, Jan 13 '12 at 23:56

MJB · Answer 1 · 2012-01-15T21:03:24.610

0

Something like

SELECT * FROM users where CONCAT(firstname, ' ', lastname) like '%$q%'

Or

SELECT * FROM users where CONCAT_WS(' ', firstname, lastname) like '%$q%'

And if reversing is desirable, try this:

SELECT * FROM users where CONCAT_WS(' ', firstname, lastname) like '%$q%'
                       or CONCAT_WS(' ', lastname, firstname) like '%$q%'

(that is, if searching for "A B" should return "A B" as well as "B A")

edited Jan 15 '12 at 21:03

answered Jan 15 '12 at 20:52

MJB

7,639
2
31
41

It should *not* return "John Doe" when the query is for "Doe John". If it should, then you have a different problem. I missed that part of it. Please confirm that you want it that way and I will try that. – MJB Jan 15 '12 at 21:04

score 0 · Answer 2 · answered Jan 15 '12 at 21:02

0

I recommend that you bind the variables. You are exposed to sql injections otherwise.

$stmt = $mysqli->prepare("select * from users where firstname like ? AND lastname like ?");
$stmt->bind_param('ss', $firstname,$lastname);

answered Jan 15 '12 at 21:02

Assaf Karmon

915
1
10
23

This is a useful suggestion, but not really related to the question. – MJB Jan 16 '12 at 13:16

kaklon · Answer 3 · 2012-01-15T21:16:43.133

0

you have to split your query string and search for each terms

$query_terms = explode(" ", $q1);
$conditions = ''
foreach($query_terms as $term){
    $conditions = $conditions.' firstname LIKE "%'.$term.'%" OR lastname LIKE "%'.$term.'%"';
}

$sql = "select DISTINCT users.*, user_id FROM users WHERE $email_filter $conditions ORDER BY lastname";

edited Jan 15 '12 at 21:16

answered Jan 15 '12 at 21:05

kaklon

2,422
2
26
39

join 2 fields in mysql to search both at the same time

3 Answers3