CODEWITHSUNDEEP
mysqlsearchpdo
Schwesi
Schwesi

Reputation: 4904

MYSQL - Search in 2 columns with one search term

I have a search query to search my users table for the search term entered by the user.

My query searches in 3 rows:

user_last_name
user_first_name
user_business_address

Everything works fine as long as the user searches for one of the rows at a time.

Let's say user_first_name is 'Alex' and user_last_name is 'Smith'. If I search for 'Alex' or 'Smith' everything works fine.

The Problem

When the user searches for 'Alex Smith' as soon as the space key is hit there are no results shown anymore.

How can I search in both rows?

How do I deal with the whitespace?

My search query

$term = strip_tags('%'.$_GET['term'].'%');

$query = $database->prepare("SELECT COUNT(*) FROM users WHERE user_last_name LIKE :term OR user_first_name LIKE :term OR user_business_address LIKE :term ORDER BY user_id  LIMIT 10");
$query->execute(array(':term' => $term));

$count = $query->fetchColumn();

if ($count >= 1) {
    $query2 = $database->prepare("SELECT * FROM users WHERE user_last_name LIKE :term OR user_first_name LIKE :term OR user_business_address LIKE :term ORDER BY user_id  LIMIT 10");
    $query2->execute(array(':term' => $term));

    $results = $query2->fetchAll();
}

Please let me know if you would like to see any more code.

I would be very thankful for any kind of help!

Upvotes: 1

Views: 32

Answers (2)

sagi
sagi

Reputation: 40481

First of all, don't use LIKE to look for an exact match, use a single equal sign (=) .

Secondly, you can use CONCAT to combine first name and last name :

SELECT * FROM users 
WHERE concat(user_first_name,' ',user_last_name) = :term 
ORDER BY user_id  LIMIT 10

I removed the last condition user_business_address like :term since you didn't explain what is it for.

If the user will input either first name/last name or full name, then you can use the LIKE operation with % wildcard :

SELECT * FROM users
WHERE concat(user_first_name,' ',user_last_name) LIKE concat('%',:term,'%')
ORDER BY user_id  LIMIT 10"

Here is a document on like operator .

Upvotes: 2

Naruto
Naruto

Reputation: 4329

SELECT * from users where user_first_name = SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 1), ' ', -1) ,
TRIM( SUBSTR(fullname, LOCATE(' ', fullname)) ) AS last_name FROM registeredusers

There's a bunch of cool things you can do with substr, locate, substring_index, etc. Check the manual for some real confusion. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Upvotes: 1

Related Questions