Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Mysql Search for contact

Posted on 2014-12-27
6
Medium Priority
?
258 Views
Last Modified: 2015-05-19
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}
0
Comment
Question by:ido90
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40519951
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?
0
 

Author Comment

by:ido90
ID: 40519961
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.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40519971
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.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 58

Accepted Solution

by:
Gary earned 1000 total points
ID: 40520069
Where you have a multi column search with variable input then you are always going to have rows returned that are not what is being looked for.


Instead of some random global search why not have a radio button field to select what you want to search by - people id, first name, company name etc.


Would make your sql significantly smaller and more on point as to what peple are searching by - even Google has various options to narrow your search by what you really want.


This is a common practice - it certainly isn't expected that you enter a search term and the whole database is searched - it puts unnecessary strain on the db.


You could even narrow it down to just ID, name, phone or email


Doesn't need to be unneccesarily complicated for the user to select the correct criteria to search by
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 40520728
I don't know why you tagged this as Foxpro database, maybe just in error, but nevertheless I also do some PHP/mysql.

peopleFirstName LIKE {$search1}%
If $search1 would be 'Ray' that would be substituted as in
peopleFirstName LIKE Ray%

Open in new window

Which is wrong syntax, you need
peopleFirstName LIKE 'Ray%'

Open in new window


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 ?+'%'

Open in new window

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.
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 40616940
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question