CODEWITHSUNDEEP
phpmysqlsearchpdo
user1621727
user1621727

Reputation:

PHP Mysql Search Query

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

Upvotes: 3

Views: 17952

Answers (5)

Mavericks
Mavericks

Reputation: 7

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

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

Upvotes: 0

Popnoodles
Popnoodles

Reputation: 28409

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;

Upvotes: 3

Phius
Phius

Reputation: 724

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%") );

Upvotes: 0

Green Black
Green Black

Reputation: 5084

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";

Upvotes: 11

E_p
E_p

Reputation: 3144

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/

Upvotes: 1

Related Questions