Search customer by last name in database

Question

function search_customer($search) { 
       global $db;
       $query = 'SELECT * FROM customers WHERE lastName LIKE :search';

       $statement = $db->prepare($query);
       $statement->bindValue(':search', $search);
       $statement->execute(); 
       $results = $statement->fetchAll();
       $statement->closeCursor();
       return $results;
}

This code gives me a table of customers with the lastName that the user puts into the search field. But it obviously only works when you put in the full last name. Can anyone tell me how I have to change the code to get results too if I only enter for example the first letter of the last name ?

It doesn't I've tried that but that it gives me an error that says division by zero — , Apr 03 '17 at 04:42
Also you have some mistake with defining `function` and error with `return $ result`. You should update that errors! @SelinaSchuh — Sumon Sarker, Apr 03 '17 at 07:10
Are you talking about the space between the dollar sign and return? just a typing mistake because i entered it with my phone ...its working fine in the application thanks :) — , Apr 03 '17 at 07:21
Also the function naming in not valid for `PHP` @SelinaSchuh — Sumon Sarker, Apr 03 '17 at 07:28
Yep i noticed that thanks it's supposed to be function search_customer — , Apr 03 '17 at 07:36

score 1 · Accepted Answer · edited May 23 '17 at 12:32

A MySQL solution

Depending on how you want the match to work, you could do something like the following:

$query = 'SELECT * FROM customers
              WHERE lastName 
              LIKE CONCAT("%", :search, "%" )';

That would return rows where lastName had :search as a substring. CONCAT() is a variadic function which combines all of it's arguments into a single string.

CONCAT("%", "John", "%") = "%John%"

How it works

Why are the percents useful? MySQL's LIKE is a pattern matching keyword. LIKE has 2 special characters "%" and "_" which are used to signify "match any group of 0 or more characters" and "match any single character" respectively.

For example:

"%john%" would match "johnson", "john", or "baker-johnson"
"joh_" would match "john", but NOT "joh"

If you just care about matching rows whose last names start with a specific string, you would remove the first "%". This would have better performance and be able to make use of indexes if you make them.

Also, as noted in another answer, this concatenation could be performed in PHP if desired. This has the benefit of being more versatile if you wanted to let your users select the type of match to use (e.g. "Search whole string" vs. "Search from beginning of string")

Escape those wildcards!

If you do use a construct like this, be sure to escape LIKE's wildcard characters ("%" and "_") unless you want your users to be able to use the wildcard characters themselves. If you do let them use them you should add a LIMIT clause to your statement, or you run the risk of someone entering "%" and returning every row in your table.

If you want to escape things you can use a method like that provided in this answer: https://stackoverflow.com/a/5020292

You could easily use PHP's str_replace() combined with the linked answer to perform your escapes. For example:

function search_customer($search) { 
   global $db;
   $query = 'SELECT * FROM customers
                 WHERE lastName 
                 LIKE CONCAT("%", :search, "%" ) ESCAPE "|"';

   $escapedSearch = str_replace($search, ["%","_"], ["|%","|_"]);

   $statement = $db->prepare($query);
   $statement->bindValue(':search', $escapedSearch);
   $statement->execute(); 
   $results = $statement->fetchAll();
   $statement->closeCursor();
   return $results;
}

Thanks its working with the CONCAT. What exactly does CONCAT mean or do tho? only new to php and coding in general I've never seen it before — , Apr 03 '17 at 07:23
@SelinaSchuh, I have updated my answer with more information. — Josiah Hudson, Apr 03 '17 at 12:33

Sumon Sarker · Answer 2 · 2017-04-03T08:56:53.877

1

You can also update your function like as -

function SearchCustomer($search) { 
   global $db;
   $query = 'SELECT * FROM customers WHERE lastName LIKE :search';

   $statement = $db->prepare($query);
   $statement->bindValue(':search', '%'.$search.'%'); #Concatenate '%' sign with search String 
   $statement->execute(); 
   $results = $statement->fetchAll();
   $statement->closeCursor();
   return $results;
}

Note : Concatenate % sign with search string, when binding value

OR

Use MySQL CONCAT() to update your query

SELECT * FROM customers WHERE lastName LIKE CONCAT("%",:search,"%")

Here is the details about MySQL CONCAT

edited Apr 03 '17 at 08:56

answered Apr 03 '17 at 07:01

Sumon Sarker

2,707
1
23
36

Ok so I guess the first version would display a table with all costumers from the database and when i enter a letter in the search field it will only display the ones that contain that letter is that right? – Apr 03 '17 at 07:26
MySQL `LIKE` clause work with string matching. Here is the details about `LIKE` clause https://www.w3schools.com/sql/sql_like.asp @SelinaSchuh – Sumon Sarker Apr 03 '17 at 07:31

Search customer by last name in database

2 Answers2

A MySQL solution

How it works

Escape those wildcards!