CODEWITHSUNDEEP
mysqlselect
Elizabeth
Elizabeth

Reputation: 173

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

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.

Upvotes: 1

Views: 3506

Answers (4)

Tom_Corbett
Tom_Corbett

Reputation: 21

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

Upvotes: 1

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36661

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

Upvotes: 1

juergen d
juergen d

Reputation: 204854

Try CONCAT

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

Upvotes: 1

knittl
knittl

Reputation: 265546

Use the CONCAT function:

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

Upvotes: 2

Related Questions