joining two columns in a select statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Wilbo
Forum Newbie
Posts: 22
Joined: Fri Jul 25, 2008 5:45 am

joining two columns in a select statement

Post by Wilbo »

Hi there,

I'm trying to make a simple search engine to search a list of names in a database.
The names are stored in two columns, first_name and last_name.
I plan on using a simple LIKE statement using the exploded results from the search box to create the query.

But because the first names and last names are stored in separate columns I want to concatinate the two columns BEFORE I apply the LIKE condition so its as if I'm searching for results from a single column.

I thought of using:

Code: Select all

SELECT CONCAT(first_name, last_name) AS name FROM users WHERE name LIKE '%john%' OR name LIKE '%smith%'
but this doesn't work.

Can anyone tell me how to do this?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: joining two columns in a select statement

Post by AbraCadaver »

Not tested, but I think you have to cancat them again in the where clause:

[text]SELECT CONCAT(first_name, last_name) AS name FROM users
WHERE CONCAT(first_name, last_name) LIKE '%john%' OR CONCAT(first_name, last_name) LIKE '%smith%'[/text]
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Wilbo
Forum Newbie
Posts: 22
Joined: Fri Jul 25, 2008 5:45 am

Re: joining two columns in a select statement

Post by Wilbo »

Thanks, that worked a treat!
Post Reply