mysql SELECT * FROM table WHERE column+space+column LIKE '%query%'

Question

For a search function, I need to be able to get the mysql function to show up results by combining two columns.

To be more precise: I have a table in the database called "members" in that there is a column called "firstname" and one called "lastname" which I need to use for this. When someone types in a whole name, eg. Jane Doe, I want the query to look whether combining firstname + a space(&nbsp?) + lastname brings forth any results.

My code so far is this:

$poster = mysql_query("SELECT id FROM members WHERE (firstname'&nbsp'lastname) LIKE '%$search%'") or die(mysql_error());
$poster = mysql_fetch_object($poster);

I know that's probably wrong, it's the most recent I've tried after trying with brackets around the firstname + lastname bits, etc, etc... But yes, any help would be greatly appreciated.

knittl · Accepted Answer · 2012-03-02 12:11:05Z

2

Use the CONCAT function:

SELECT id FROM members
WHERE CONCAT(firstname, ' ', lastname) LIKE ?

answered Mar 2, 2012 at 12:11

knittl

269k59 gold badges339 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Elizabeth Over a year ago

Thanks so much! I'll note down the CONCAT function, hadn't heard of it before

juergen d · Accepted Answer · 2012-03-02 12:11:54Z

1

Try CONCAT

SELECT id FROM members WHERE concat(firstname, ' ', lastname) LIKE '%$search%'

answered Mar 2, 2012 at 12:11

juergen d

205k40 gold badges303 silver badges377 bronze badges

Comments

Vishwanath Dalvi · Accepted Answer · 2012-03-02 12:12:15Z

1

SELECT id FROM members WHERE (firstname + ' ' + lastname) LIKE '%$search%'

answered Mar 2, 2012 at 12:12

Vishwanath Dalvi

36.9k41 gold badges128 silver badges156 bronze badges

Comments

Tom_Corbett · Accepted Answer · 2012-03-02 12:28:29Z

I believe what you are looking for is:

$name_var = 'John Doe';

// Example 1 - using concatenation
$sql = "SELECT id FROM members WHERE CONCAT(firstname, ' ', lastname) = '$name_var'";

The above statement will search for everything where the first name is John and the last name is Doe

This is rather ineficcient as it will have to evaluate the CONCAT everytime in mysql I believe

I would reccomend validating in PHP that the string is two words as you expect e.g.

$name_var = 'John Doe';

// this will split the string based on spaces
$names = explode(' ', $name_var);
$first_name = $names[0];
$last_name  = $names[1];

// Example 2 - searching each field
$sql = "SELECT id FROM members WHERE firstname = '$first_name' AND lastname = '$last_name'";

The above statement will still search for everything where the first name is John and the last name is Doe

In the above statment you are actually just searching based on the exact values so it is much more efficient. If this query is going to be ran regularly, you should also add indexes to the firstname and lastname fields in your mysql table as it will greatly increase the speed!

hope this helps!

Tom

Collectives™ on Stack Overflow

mysql SELECT * FROM table WHERE column+space+column LIKE '%query%'

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related