ido90
asked on
Mysql Search for contact
I am trying to find the best way to create a global search of contacts. To have one search box and search within multiple fields at once. Im using mysql with InnoDB engine.
$wherestm = "
WHERE (people.peopleFirstName LIKE {$search1}%) OR
(people.peopleLastName LIKE {$search1}%) OR
(company LIKE {$search1}%) OR
(people.peopleTel LIKE {$search1}%) OR
(people.peopleEmail LIKE {$search1}%)";
}
$sql =
"
SELECT
people.peopleId,
people.peopleFirstName,
people.peopleLastName,
people.peopleCompanyId,
company.companyName As company,
people.peopleTel,
people.peopleCel,
people.peopleEmail
FROM
people
LEFT JOIN company ON people.peopleCompanyId = company.companyId
{$wherestm}
$wherestm = "
WHERE (people.peopleFirstName LIKE {$search1}%) OR
(people.peopleLastName LIKE {$search1}%) OR
(company LIKE {$search1}%) OR
(people.peopleTel LIKE {$search1}%) OR
(people.peopleEmail LIKE {$search1}%)";
}
$sql =
"
SELECT
people.peopleId,
people.peopleFirstName,
people.peopleLastName,
people.peopleCompanyId,
company.companyName As company,
people.peopleTel,
people.peopleCel,
people.peopleEmail
FROM
people
LEFT JOIN company ON people.peopleCompanyId = company.companyId
{$wherestm}
That approach seems reasonable. It may not be syntactically exact, but it appears to be conceptually sound. How many rows are in the people table? Which columns are indexed?
ASKER
Its a large data set, 30k of data. I have indexed Id, PeopleFirstName, PeopleLastName, peopleTel and peopleEmail. The issue Im running into with this approach is that for some reason Im getting results which have nothing todo with the search term.
My initial guess is that this might be the culprit:
(company LIKE {$search1}%) OR ...
However when you search for:
(people.peopleTel LIKE {$search1}%) OR
(people.peopleEmail LIKE {$search1}%)
if the $search1 value is NULL or very short, you may get a lot of results that are unwanted.
To understand this better it would be helpful if you can post some sample data - a few rows from the data set that reflect both the wanted and unwanted results. Then we can work to refine the query so that unwanted results are not returned. Let the SSCCE be your guide.
(company LIKE {$search1}%) OR ...
However when you search for:
(people.peopleTel LIKE {$search1}%) OR
(people.peopleEmail LIKE {$search1}%)
if the $search1 value is NULL or very short, you may get a lot of results that are unwanted.
To understand this better it would be helpful if you can post some sample data - a few rows from the data set that reflect both the wanted and unwanted results. Then we can work to refine the query so that unwanted results are not returned. Let the SSCCE be your guide.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't know why you tagged this as Foxpro database, maybe just in error, but nevertheless I also do some PHP/mysql.
Maybe my MySQL is rusted and this works, but I'm quite sure you only can make a more generic approach with parametrization with the questionmark, see mysqli_stmt_bind_param - http://php.net/manual/de/mysqli-stmt.bind-param.php, you'd write the query parameterized with
Or you add the % to the variable value and simplify the sql to clauses using LIKE ? and let your frontend application make the decision, whether to add % to the user input or not. For example % doesn't work at all for numeric fields, as LIKE only is a string comparison operator.
Bye, Olaf.
peopleFirstName LIKE {$search1}%If $search1 would be 'Ray' that would be substituted as in
peopleFirstName LIKE Ray%
Which is wrong syntax, you need peopleFirstName LIKE 'Ray%'
Maybe my MySQL is rusted and this works, but I'm quite sure you only can make a more generic approach with parametrization with the questionmark, see mysqli_stmt_bind_param - http://php.net/manual/de/mysqli-stmt.bind-param.php, you'd write the query parameterized with
peopleFirstName LIKE ?+'%'
And more such parameters and bind the $search1 variable multiple times.Or you add the % to the variable value and simplify the sql to clauses using LIKE ? and let your frontend application make the decision, whether to add % to the user input or not. For example % doesn't work at all for numeric fields, as LIKE only is a string comparison operator.
Bye, Olaf.
I would check to see if the input has alphanumeric characters. If it does then exclude telephone and if it does not then search telephone columns and exclude names. It will impact performance.