3

Hello i have a simple search query, what i'm facing is when someone writes the only first name of the user that he wants to search, my query finds it, also when someone only writes the last name in the input and posts it, it also shows that too, but when user writes first name and last name together in the input, it can't find the user even he/she exists. The last part of $q query where i wrote first name and last name like part doesnt work i know there my logic is bad, but how can i fix that

    try {
        $q = "SELECT * FROM `members` WHERE `first_name` LIKE :search_string OR `last_name` LIKE :search_string OR `first_name` AND `last_name` LIKE :search_string";
        $q_do = $db->prepare($q);
        $q_do->execute( array("search_string"=>'%'.$query.'%') );
        $number = $db->query("SELECT FOUND_ROWS()")->fetchColumn();
    } catch(PDOException $e) {
        $log->logError($e." - ".basename(__FILE__));
    }

Thank you

Improve this question
1
  • it is what user writes into search input. Commented Nov 23, 2012 at 18:19

5 Answers 5

Reset to default
11

Try using concat:

$q = "SELECT * FROM `members` WHERE `first_name` LIKE :search_string 
OR `last_name` LIKE     :search_string 
OR concat(`first_name` , ' ', `last_name`) LIKE :search_string";
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

+1 for a clever answer that will likely work fine for what the asker wants to do, but a full-text search is still a much better approach.
Sure, but there can be names with less than 4 characters, you have to edit your my.cnf for that. You can add a combined index on both fields to keep it fast with a lot of rows.
Full text search, or any search engine, would be a better choice. As those quries tend to become complex very fast.
3 
SELECT * 
FROM `members` 
WHERE `first_name` LIKE :search_string 
   OR `last_name` LIKE :search_string 
   OR `first_name` AND `last_name` LIKE :search_string;

ANDis an operator not a concatenator. 

SELECT * 
FROM `members` 
WHERE `first_name` LIKE :search_string 
   OR `last_name` LIKE :search_string 
   OR CONCAT(`first_name`,' ', `last_name`) LIKE :search_string;
Improve this answer

Comments

1

So what you do no is:

User enters 'First Last'

You search :

First like '%First Last%' or Last  like '%First Last%' ...

You need to use full text search index.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

or something like

http://sphinxsearch.com/

Improve this answer

Comments

0

Try this:

$query = explode(" ", $query);
if(count($query)>1){
    $fname = $query[0];
    $lname = end($query);
}else{
    $fname = $query[0];
    $lname = $query[0];
}
$q = "SELECT * FROM `members` WHERE `first_name` LIKE :fname OR `last_name` LIKE :lname";
$q_do = $db->prepare($q);
$q_do->execute( array('fname' => "%$fname%", 'lname' => "%$lname%") );
Improve this answer

1 Comment

Never do this. Use proper tools. Full text search any search engine would do better. Your solution would create more problems!
0

The simplest Search Query for you.. Try this its working man.

SELECT * FROM TableName WHERE title like '%Your Search Text%'

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.