I hope you can help :)
This is how the table looks:
+------------+----------------+------------------+---------+
| firstName | lastName | email | etc... |
+------------+----------------+------------------+---------+
| John | Doe | [email protected] | etc... |
+------------+----------------+------------------+---------+
| John | Michaels | [email protected] | etc... |
+------------+----------------+------------------+---------+
This is how the code looks:
if($_GET['search-customers'] != '') {
$busqueda = $_GET['search-customers'];
$query->andWhere("(c.firstName LIKE '%$busqueda%' OR c.lastName LIKE '%$busqueda%' OR c.email LIKE '%$busqueda%')");
}
With that QUERY:
All right, I understand, When I type "John D", it try to find first in firstName (doesn't match) and also it doesn't match lastName or email.
How can I combine them?
The idea its to find the complete string in all possibilities.
Thanks!
I will provide you a different alternative using MySQL's Full-Text Search Functions. Lets begin to prepare the table:
ALTER TABLE persons ADD FULLTEXT (`firstname`, `lastname`);
Now, firstname
and lastname
are columns to be used by full-text in order to search for matches:
SELECT * FROM persons
WHERE MATCH (firstname,lastname)
AGAINST ('John D' IN NATURAL LANGUAGE MODE);
The result will be:
+------------+----------------+------------------+---------+
| firstName | lastName | email | etc... |
+------------+----------------+------------------+---------+
| John | Doe | [email protected] | etc... |
+------------+----------------+------------------+---------+
| John | Michaels | [email protected] | etc... |
+------------+----------------+------------------+---------+
Why both? Because John
(as a word) was found, however John Doe
is in the first row because has much similitude with the term of search.
Say, that lets apply this tool with Doctrine. I will assume that your model looks like this:
class Person{
/** @column(type="string", name="firstname")*/
protected $firstName;
/** @column(type="string", name="lastname")*/
protected $lastName;
/** @column(type="string")*/
protected $email;
}
Lets create the search function:
public function search($term){
$rsm = new ResultSetMapping();
// Specify the object type to be returned in results
$rsm->addEntityResult('Models\Person', 'p');
// references each attribute with table's columns
$rsm->addFieldResult('p', 'firstName', 'firstName');
$rsm->addFieldResult('p', 'lastName', 'lastname');
$rsm->addFieldResult('p', 'email', 'email');
// create a native query
$sql = 'select p.firstName, p.lastname, p.email from persons p
where match(p.firstname, p.lastname) against(?)';
// execute the query
$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $term);
// getting the results
return $query->getResult();
}
Finnally, and example:
$term = 'John D';
$results = search($term);
// two results
echo count($results);
Additional notes:
MyISAM
tables.CHAR
, VARCHAR
, or TEXT
columns. IN NATURAL LANGUAGE MODE
in a search, mysql returns an empty resultse when the results represent < 50%
of the records.Maybe you could use the explode
function like this:
$busqueda = $_GET['search-customers'];
$names = explode(' ',$busqueda);
if(count($names)>1){
$query->andWhere("(c.firstName LIKE '%{$names[0]}%' AND c.lastName LIKE '%{$names[1]}%')");
}else{
$query->andWhere("(c.firstName LIKE '%$busqueda%' OR c.lastName LIKE '%$busqueda%' OR c.email LIKE '%$busqueda%')");
}
but, using like %word%
is inefficient, because it can't use index.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With